cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WindAnalytics
Helper I
Helper I

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

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) 

 

 

demo.png

 

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

7 REPLIES 7
Eyelyn9
Community Support
Community Support

Hi @WindAnalytics ,

 

Sorry for my mistake, please try:

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

Eyelyn9_0-1638772658433.png

 

 

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.

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

Hello again @Eyelyn9, 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:

 

e2.png

 

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.

Eyelyn9
Community Support
Community Support

Hi @WindAnalytics ,

 

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

Eyelyn9_0-1638754243423.png

 

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:

Eyelyn9_1-1638754761101.png

 

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.

Hello @Eyelyn9 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:

error.png

 

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

 

Best regards!

WindAnalytics
Helper I
Helper I

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
Helper I
Helper I

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!