Count Permutations Within a Column Based on Slicer (DirectQuery Model)

[SEDE]

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!

[lbendlin]

Does the result have to come from a single table or can the slicer be fed from a disconnected table?

[SEDE]

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