DAX Measure stopped working after expanding list into rows for table

[zhona9]

I have two tables, 'Continous Improvement Projects' and 'CI Milestones'. They used to have a one to many relationship.

To use a column as a slicer, I had to do expand list to rows for 'Continuous Improvement Projects'. Due to this I had to convert the relationship to 'Many to many'.

Now my measure to get the next milestone stopped working. Any ideas how to fix this? I suppose there should be SUMMARIZE used somewhere but not sure where:

Upcoming Milestone = 
var currentproject = selectedvalue('CI Milestones'[ProjectID0])

RETURN
CALCULATE(MIN('CI Milestones'[Title]),
'CI Milestones'[Id]=
MINX(
    FILTER(ALL('CI Milestones'),'CI Milestones'[ProjectID0]=currentproject && 'CI Milestones'[MilestoneStartDate]>TODAY()),
    'CI Milestones'[Id])
)

[zhona9 · Accepted solution]

I managed to find a solution, with the help of someone in my company:

I created a new column in ContinuousImprovementProject called DistinctID, duplicating the original ID column. In Power Query, I removed duplicates on the DistinctID column. I linked the ID column in the Milestone table to the DistinctID column. Viola, problem solved.

[Dhairya]

Hey @zhona9 
For plotting slicer, instead of expanding the list to rows you can take a duplicate of that table and perform a necessary transformation on that for plotting it as a slicer, By this way you will not require to change your measure.

If this helps you then please mark my solution as accepted so that others can find it quickly while facing similar issue. Thank You!

[zhona9]

How do I take a duplicate and link the two tables?

[Dhairya]

Hey @zhona9 
You can create a duplicate of the table from the power query editor and keep only those columns that you want to plot as a slicer. After that close and apply new changes and create necessary relationships. 

If still you are facing issues then please share sample input and expected output. Thank You!

[zhona9]

Hi Dairya

I do have a table for the slicer and the original table, where the functions involved is a list. Do you mean I can create a 1 to many relationship between these two tables?

[zhona9]

Btw I tried and it doesn't work. The slicer still doesn't filter my original table.