Question

我有以下数据集:

数据集

“dataset”/

I wanted to write DAX code in which I haven t found the right logics for the following case below: The table of datasets above represent Subject Table with column of "Category" and "Date"

I have 2 slicer which are from Calendar and Comparison Calendar Table where Calendar table have relationship with Subject Table but the Comparison Calendar Table does not have active relationship with Subject Table. hen people select 2 dates (previous and later date) from Calendar and Comparison Calendar Table respectively in the provided slicer, then the DAX will categorize the Category Name into 3 clusters : 1)retired - when the later date does not have the category name from previous date but the previous date does have the category name 2)existing - when both the later date and previous date does have the same category name 3)new - when the later date does have the category name from previous date but the previous date does not have the category name

下面是数据模型的筛选:

“entergraph

预计结果如下:

“entergraph

Answer 1

You should first update the relationships to be One-to-Many and Single direction. Should look like below:

Then create a new Measure with:

Cluster = 
  var d1 = CALCULATE( COUNTROWS( Subject ), REMOVEFILTERS( Comparison Calendar ) )
  var d2 = CALCULATE( COUNTROWS( Subject ), REMOVEFILTERS( Calendar ), USERELATIONSHIP( Comparison Calendar [Date],  Subject [Date]) )
  return SWITCH( TRUE(),
    d1 > 0 && ISBLANK(d2), "retired",
    d1 > 0 && d2 > 0, "existing",
    ISBLANK(d1) && d2 > 0, "new"
  )

希望这将产生以下成果:

友情链接