Measure that counts distinct products which meet conditions specified in a table

[WindAnalytics]

Hello,

I need help with a report, it looks like the image below. Note that there is a measure in the DIM_CAT table. I'm trying to figure out how to include it with this post/thread as I'm getting this message: The file type (.pbix) is not supported. Here is a link: Demo report (link updated to extend file hosting time limit) 

Does anyone know how to go about solving this. Thanks!

[v-eqin-msft]

Hi @WindAnalytics ,

Sorry for my mistake, please try:

VAR val2 = DISTINCT( SELECTCOLUMNS(prod,"col",[PROD_ID] ))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WindAnalytics]

ADDITIONAL INFORMATION:

If I create a new variable named val3 and enter the same values manually, those values are the ones that are returned in val2 when option 3 is selected:

VAR val3 = {18, 20}

and then use val3 in CALCULATE with the IN operator as follows:

RETURN
    CALCULATE(
        DISTINCTCOUNT( FACT_PROD[PROD_ID] ),
        FACT_PROD[PROD_ID] IN val3

then it works perfectly, but with val2 it doesn't work. However, val2 and val3 work perfectly in CONCATENATEX(). I mean:

CONCATENATEX( val2, [col], ", " )

y

CONCATENATEX( val3, [Value], ", " )

produce the same result:

18, 20

The complete measure:

Giga Measure = 
VAR val = VALUES ( DIM_SEL[CONF_ID] ) -- Select "Option 3" in slicer
VAR no_val = COUNTROWS ( DIM_SEL )
VAR prod = 
    SUMMARIZE(
        FILTER(
            SUMMARIZE(
                FILTER(
                    SUMMARIZE (
                        FACT_PROD,
                        FACT_PROD[PROD_ID],
                        FACT_PROD[CONF_ID],
                        "@Count",
                            IF(FACT_PROD[CONF_ID] IN val, 1, BLANK() )
                    ),
                    [@Count] <> BLANK()
                ),
                FACT_PROD[PROD_ID],
                "@CountAgain", COUNT( FACT_PROD[PROD_ID] )
            ),
            [@CountAgain] = no_val
        ),
        FACT_PROD[PROD_ID]
    )

VAR val2 = DISTINCT( SELECTCOLUMNS( prod, "col", [PROD_ID] ) )  -- Problem is here, I want to return the resulting PROD_ID from prod variable but dont know how to.
VAR val3 = { 18, 20 }

VAR result = 
    CALCULATE(
        DISTINCTCOUNT( FACT_PROD[PROD_ID] ),
        /*FILTER(
            FACT_PROD,
            TREATAS( val2, FACT_PROD[PROD_ID] )
        )*/ -- This one doesn't work
        -- FACT_PROD[PROD_ID] IN val2 -- This one almost works
        FACT_PROD[PROD_ID] IN val3 -- this one works
    )

VAR _concat2 = CONCATENATEX( val2, [col] , ", " )
VAR _concat3 = CONCATENATEX( val3, [Value], ", " )

RETURN
    result

[WindAnalytics]

Hello again @v-eqin-msft, the IntelliSense ins't complaining anymore. Great job! So I replace the [Number of products] measure in the lower left table with the [Giga Measure] and I get the following error:

I tried then to replace this line in the RETURN section:

TREATAS( val2, FACT_PROD[PROD_ID] )

with this line:

FACT_PROD[PROD_ID] in val2

This didn't produce an error but instead returned an empty table. However when I use it in a Card visual, it seems to return the number of unique IDs considering the selected Option in the left top slicer.

However the goal is to return the tables to the right, depending on the Option selected.

[v-eqin-msft]

Hi @WindAnalytics ,

Sorry, I could not get your pbix file, it seems to have been deleted:

But based on your issue here

VAR val2 = VALUES( _prods[col] )  -- Problem is here, I want to return the resulting PROD_ID from prod variable but dont know how to.

I have built a data sample:

So you may try:

VAR val2 = DISTINCT( SELECTCOLUMNS(_prods,"col", [col]))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WindAnalytics]

Hello @v-eqin-msft and thank you for replying!

I've uploaded the demo report once again. So whenever you have the time you can take a look.

I have tried your solution but the IntelliSense is reacting like this:

Do you have any suggestions how to go about solving it?

Best regards!

[WindAnalytics]

Hello friends, I've made som progress. I think I'm close but I'm also stuck. This is the measure:

Giga Measure = 
VAR val = VALUES ( DIM_SEL[CONF_ID] )
VAR n_o_val = COUNTROWS ( DIM_SEL )
VAR prod = 
    SUMMARIZE(
        FILTER(
            SUMMARIZE(
                FILTER(
                    SUMMARIZE (
                        FACT_PROD,
                        FACT_PROD[PROD_ID],
                        FACT_PROD[CONF_ID],
                        "@Count",
                            IF(FACT_PROD[CONF_ID] IN val, 1, BLANK() )
                    ),
                    [@Count] <> BLANK()
                ),
                FACT_PROD[PROD_ID],
                "@CountAgain", COUNT( FACT_PROD[PROD_ID] )
            ),
            [@CountAgain] = no_val
        ),
        FACT_PROD[PROD_ID]
    )

VAR val2 = VALUES( _prods[col] )  -- Problem is here, I want to return the resulting PROD_ID from prod variable but dont know how to.

RETURN
    CALCULATE(
        DISTINCTCOUNT ( FACT_PROD[PROD_ID] ),
        FILTER(
            FACT_PROD,
            TREATAS( val2, FACT_PROD[PROD_ID] ) -- I then want to use those values to filter filter the distinct count
        )
    )

I'm sure this measure will produce some laughters but it was the best I could do. Right now I just need it to work. Does anyone know how to make it work?

[WindAnalytics]

Hello everyone, I have now managed to include the sample report in the post. Hopefully this will simplify a solution. Thank you!