Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
anvikuttu
Advocate I
Advocate I

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. In the below snapshot, I am trying to compare Feb against Mar  to see what fruits have newly come in Mar when compared to Feb. 

I was able to do this logic in the Calculated Table, however, i wanted this calculation to be a dynamic measure. because i wanted a dynamic comparison of two months of data.

 Below is the Dax measure I have used, which is not giving me the correct result

Flag =
VAR table1=
    CALCULATETABLE(
        VALUES(Raw_Data[Fruits]),
        FILTER(Raw_Data,Raw_Data[Month]=SELECTEDVALUE(Month_Table_1[Month]))
    )
VAR table2=    
    CALCULATETABLE(
        VALUES(Raw_Data[Fruits]),
        FILTER(Raw_Data,Raw_Data[Month]=SELECTEDVALUE(Month_Table_2[Month]))
    )
VAR final=EXCEPT(table2,table1)
return
IF(MAXX(Raw_Data,[Fruits])in final ,"Yes","No")

 

 

 

anvikuttu_0-1699809793783.png

The Flag should have populated as "Yes"  for Peach in Mar because we are comparing Feb to Mar and only "Peach" is the new fruit showing in Mar. The rest of the records should be populated as "No".

Kindly help with the logic.

 

Thanks and regards,

Vijay Krishnan

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

5 REPLIES 5
TheoC
Super User
Super User

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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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"
    )

anvikuttu_1-1699844364619.png

 

 

Hi @anvikuttu 

 

Are you able to send me the sample data you're working with (or the PBIX file)?

 

Also, I just realised that the Month1 and Month2 tables are not necessarily structured in a particular order of month (i.e. Month1 = Feb, Jan, Mar & Month2 the same).  

 

Let me know if it's possible to send through the data!  

 

Thanks heaps,

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Hi @TheoC , the modified code that I pasted previously is working fine. Moreover, I do not find the option to attach a pbix file here. I have also accepted your code as a solution.

Thanks for all your help...appreciate it.

 

Thanks and regards

@anvikuttu no worries. If at all you need further guidance, feel free to add me on LinkedIn and then send the PBIX. More than happy to assist however I can mate.

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.