Forum Discussion

anvikuttu's avatar
anvikuttu
Advocate I
2 years ago
Solved

Dax Dynamic Measure

  Hi Team, I have fact table and 2  dimension table called the months table. The months and the raw data are NOT connected to each other .Here I am trying to compare the month against the month. I...
Need Help
  • TheoC's avatar
    TheoC
    2 years ago

    Hi anvikuttu 

     

    Try to modify the way in which you use the EXCEPT function.  I haven't tested the below but give it a go and modify to your needs:

    New Fruits Flag = 

VAR SlicerMonth1 = SELECTEDVALUE( Month_Table_1[Month] )

VAR SlicerMonth2 = SELECTEDVALUE( Month_Table_2[Month] )

VAR FruitsInMonth1 = 

    CALCULATETABLE (
        VALUES( Raw_Data[Fruits] ) ,
        Raw_Data[Month] = SlicerMonth1
    )
	
VAR FruitsInMonth2 = 
    
	CALCULATETABLE(
        VALUES( Raw_Data[Fruits] ) ,
        Raw_Data[Month] = SlicerMonth2
    )
	
VAR NewFruitsInMonth2 = EXCEPT ( FruitsInMonth2 , FruitsInMonth1 )
VAR CurrentFruit = SELECTEDVALUE ( Raw_Data[Fruits] )

RETURN

IF ( 
	CurrentFruit IN NewFruitsInMonth2 , 
	"Yes", "No"
	)

     

    Make sure to place this measure in the visual where you have the fruits listed down and it should dynamically show "Yes" for new fruits in the selected comparison months.

     

    Hoping this helps mate.

     

    Theo

     

TheoC's avatar
TheoC
Community Champion
2 years ago

Hi anvikuttu 

 

Try to modify the way in which you use the EXCEPT function.  I haven't tested the below but give it a go and modify to your needs:

New Fruits Flag = 

VAR SlicerMonth1 = SELECTEDVALUE( Month_Table_1[Month] )

VAR SlicerMonth2 = SELECTEDVALUE( Month_Table_2[Month] )

VAR FruitsInMonth1 = 

    CALCULATETABLE (
        VALUES( Raw_Data[Fruits] ) ,
        Raw_Data[Month] = SlicerMonth1
    )
	
VAR FruitsInMonth2 = 
    
	CALCULATETABLE(
        VALUES( Raw_Data[Fruits] ) ,
        Raw_Data[Month] = SlicerMonth2
    )
	
VAR NewFruitsInMonth2 = EXCEPT ( FruitsInMonth2 , FruitsInMonth1 )
VAR CurrentFruit = SELECTEDVALUE ( Raw_Data[Fruits] )

RETURN

IF ( 
	CurrentFruit IN NewFruitsInMonth2 , 
	"Yes", "No"
	)

 

Make sure to place this measure in the visual where you have the fruits listed down and it should dynamically show "Yes" for new fruits in the selected comparison months.

 

Hoping this helps mate.

 

Theo

 

anvikuttu's avatar
anvikuttu
Advocate I
2 years ago

Thank you TheoC  for your quick reply.  Your code partly fixes the problem.

The resultant dataset should be based on Month2. If you see below (snapshot) first thing is we are trying to see what fruits are available in Month2 which is not available in Month1. The flags populated for Feb is correct,

however we also see that there is flag = Yes for Mar as well which is incorrect.
I have slightly modified your code

New Fruits Flag =

VAR SlicerMonth1 = SELECTEDVALUE( Month_Table_1[Month] )

VAR SlicerMonth2 = SELECTEDVALUE( Month_Table_2[Month] )

VAR FruitsInMonth1 =

    CALCULATETABLE (
        VALUES( Raw_Data[Fruits] ) ,
        Raw_Data[Month] = SlicerMonth1
    )
   
VAR FruitsInMonth2 =
   
    CALCULATETABLE(
        VALUES( Raw_Data[Fruits] ) ,
        Raw_Data[Month] = SlicerMonth2
    )
   
VAR NewFruitsInMonth2 = EXCEPT ( FruitsInMonth2 , FruitsInMonth1 )
VAR CurrentFruit = SELECTEDVALUE ( Raw_Data[Fruits] )
VAR CurrentMonth= SELECTEDVALUE(Raw_Data[Month])

RETURN

IF (
    CurrentFruit IN NewFruitsInMonth2 &&  CurrentMonth = SlicerMonth2,
    "Yes", "No"
    )