Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All!
Very new to Power BI here, but I hope you all can help.
I had set up some sample data to experiment with for getting the result I hoped for. I thought I got it, but then when applying it to my real data, I end up with a “Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models” error. So it looks like I’m back to the drawing board!
I’m basically trying to count permutations within my data based on selections in a slicer. You can see the functionality here:
Here’s my sample data:
Clothes:
Form ID | Question | Answer |
A32f | Color | Red |
A32f | Fit | Tight |
A32f | Style | Low Cut |
G34d | Color | Blue |
G34d | Fit | Tight |
G34d | Style | High Waist |
K86p | Color | Red |
K86p | Fit | Loose |
K86p | Style | High Waist |
With a lot of googling and fiddling, I ended up with two measures and a calculated column.
(Measure 1) CountAnswers:
CountAnswers =
VAR _NoSelection =
NOT CALCULATE ( ISFILTERED ( Clothes[Answer] ), ALLSELECTED ( Clothes[Answer] ) )
RETURN
IF (
NOT ( _NoSelection ),
IF (
CALCULATE ( DISTINCTCOUNT ( Clothes[Answer] ), ALLSELECTED ( Clothes[Answer] ))
= COUNTROWS ( ALLSELECTED ( Clothes[Answer] ) ),
1
)
)
(Measure 2) CountAnswersTotal:
CountAnswersSUM =
SUMX ( DISTINCT ( Clothes[Form ID] ), [CountAnswers] )
(Column) Combined:
Combined = CALCULATE(CONCATENATEX(Clothes,Clothes[Answer], ", "), FILTER(ALL(Clothes),Clothes[Form ID] = EARLIER(Clothes[Form ID])))
Is there any way to recreate the functionality here that will work on DirectQuery model?
Thanks!
Does the result have to come from a single table or can the slicer be fed from a disconnected table?
Hi Ibendlin,
In theory I can do whatever I need to do with manipulating the data layout as long as the data itself is good. It does currently all reside in one table with many columns, though I only need the three listed above for this, (I assume).
Thanks! I appreciate any direction you can give.
SEDE
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |