The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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
User | Count |
---|---|
15 | |
12 | |
8 | |
7 | |
7 |
User | Count |
---|---|
24 | |
20 | |
12 | |
10 | |
7 |