Duplicated when using multiple fact tables

DAX
1

Hi All,
I’m challenging myself trying to solve a riddle just using DAX.
I have a model shaped like this. Production is a bit more complex, but this essentially fits the purpose.

image
image970×697 21.8 KB

The Compositions ID table lists the unique IDs from Compositions (the one in production has many more attributes).

Now I want to list in a table the Composition attribute from the Compositions ID table, one or more attributes from the Compositions table (in this case just Composition), and the number of colors and fruits.

When I do it I get duplicated in the table. I’ve tried table expansion, virtual relations using TREATAS and CROSSFILTER to activate relations between tables Compositions and Color and Fruits, but the issue persists.

I know that a good model would simplify or sometimes eliminate the need for complex DAX, but as I said this is a challenge, I ventured into, maybe without the proper knowledge.

Does anyone have an idea how to face this?

Thanks for any support.

Roberto

model test.xlsx (305.9 KB)
test.pbix (32.8 KB)

2

Hi @Roberto - Please check if solution provided in attached file fulfil your requirement.

test (2).pbix (33.6 KB)

Thanks
Ankit J

3

Hi @ankit it worked and I could apply it in the model.

Unfortunately, the current report has slicers on the fact tables.
How should I change the measures in this case?

I’ve tried this but doesn’t work.

Thanks

Count Distinct Color with Filter on Color =
VAR ColorSelected = SELECTEDVALUE(Colors[Color])

VAR Result =
CALCULATE(
countrows(SUMMARIZE(filter(ALLSELECTED(Colors),Colors[ID] = SELECTEDVALUE(Compositions[ID])),Colors[Color])),
CROSSFILTER(Colors[ID], ‘Compositions ID’[ID], Both),
CROSSFILTER(‘Compositions ID’[ID], Compositions[ID], Both)
)
RETURN
Result

test (2).pbix (34.2 KB)

4

Will check and see if I can help.

5

Hi @Roberto - The measures I provided seems to be interacting with the Slicers on new page. Please provide more information on what is needed or what is not working.

Additionally will request in future, try to put the entire requirements in one go and not in parts as this becomes difficult/time consuming for the person helping on the posts and will bring delays for you also.

Thanks
Ankit J

6

hi @ankit,
thanks for your support.

I’ll try to make my ask more clear.
In the model, I have slicers on colors and fruit.

image

The behavior of the report is correct when I select one value of those slicers, showing only the lines with values.
In the case below picking red the “Correct Count Distinct Color” shows no values, so the Correct Count Distinct Fruit.

I’d need the table to show no rows since one of the slicers (in this case the color) produces no values.
In short I need to replicate the filter on the slicer like I had a one-to-many relation between dimension tables Colors or Fruit, and a fact table.

Thanks,
Roberto

image

Test.pbix (34.8 KB)

7

Hi @Roberto - Do check if attached solution, fulfils the requirements. Check measures with _New.

test (2) (1).pbix (34.9 KB)

Thanks
Ankit J

2 Likes
8

Hi @ankit,
I got the logic.
Since I have three tables (color, fruit, and one more) in the real model, I’ll just have to add a new section and add to the IF one more condition.

Just wanted to thank you for your time.

All the best,
Roberto

Count Distinct Color_New =
VAR FruitCount =
COUNTROWS (
SUMMARIZE (
FILTER (
ALLSELECTED ( Fruits ),
Fruits[ID] = SELECTEDVALUE ( Compositions[ID] ) // filtering with row context
),
Fruits[Fruit]
)
)
VAR colorCount =
COUNTROWS (
SUMMARIZE (
FILTER (
ALLSELECTED ( Colors ),
Colors[ID] = SELECTEDVALUE ( Compositions[ID] ) // filtering with row context
),
Colors[Color]
)
)
RETURN
IF ( ISBLANK ( FruitCount ), BLANK (), colorCount )

2 Likes