Counting a measure return

[Anonymous]

Hello,

I'm linking to a previous post of mine to help give more context if needed.

https://community.powerbi.com/t5/Desktop/Measures-affected-by-slicer-choice/m-p/595019#M282535

I have a follow up question. I am trying to determine how to create a measure that will count the returns from another measure. For example:

I have the following data, in which I determined how to find the minimum price per item. The data I'm using has a similar structure to this:

 ITEM ID     SELLER     PRICE

     1                A           $50

     1                B           $40

     1                C           $45

     2                A           $100

     2                C           $120

     3                A           $10

     3                B           $11

     3                C            $9

I use the following measure to return the minimum price seller for each item based upon sellers selected in a slicer.

MinPriceSeller = VAR minprice = CALCULATE ( MIN ( 'Table'[PRICE] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ITEM ID] = SELECTEDVALUE ( 'Table'[ITEM ID] ) ) ) RETURN CALCULATE ( SELECTEDVALUE ( 'Table'[SELLER] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[PRICE] = minprice ) )

So my return is technically a string value.

The output (with all sellers selected in a slicer), when put into a table visual, looks like the following:

ITEM ID        MinPriceSeller        PRICE

    1                        B                      $40

    2                        A                     $100

    3                        C                       $9

The output (with only sellers A and C selected in a slicer), when put into a table visual, looks like the following:

ITEM ID        MinPriceSeller        PRICE

    1                        C                      $45

    2                        A                     $100

    3                        C                       $9

What I am attempting to do now is creature another measure that will count the IDs that correspond to each "MinPriceSeller", returning the count, and then be able to visualize this in a clustered column chart. Aka, I want this measure to show MinPriceSeller has 2 Cs and 1 A in the previous example. This chart would have the MinPriceSeller on the X-axis and the count of the IDs on the Y-axis. I can't seem to find a way to return the correct count.

Thank you, and let me know if you need more information!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please try a solution like below. 

1. Create a new table [Sellers].

Sellers = VALUES('Table'[SELLER])

2. Create a measure.

Measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[ITEM ID], 'Table'[SELLER], 'Table'[PRICE] ),
        "ifMinPrice", IF (
            [PRICE]
                = CALCULATE (
                    MIN ( 'Table'[PRICE] ),
                    ALL ( 'Table'[SELLER], 'Table'[PRICE] ),
                    ALLSELECTED ( 'Sellers'[SELLER] )
                ),
            1,
            0
        )
    ),
    [ifMinPrice]
)

Best Regards,

[Anonymous]

Hello @v-jiascu-msft

Thanks for the response.

I applied this to my data, and it works when I do not filter any sellers out in my slicer.

When something is selected in my slicer, it only filters to show the value from "all selected", not what the results would be when including all the selected sellers to find the minimum price seller.

The original table where the lowest price seller is showing the correct data, but not the visual I created from the most recent measure.

Let me know if that's not clear. Thanks!

[v-jiascu-msft]

Hi @Anonymous,

Do you have an independent table [Sellers] that the slicer is from? If you have done all the steps in my last post, please share a more accurate sample. Please mask the sensitive parts first.

Best Regards,

[Anonymous]

Hello @v-jiascu-msft,

When I use the filter from the separate Sellers table, nothing will filter on the page. I would have a hard time sending the data and covering anything sensitive. I would basically have to cover everything. I created a report with data that is very similar to my own, but not actual data. I have put in the measures and visuals already exactly how it is set up in my actual report and the data types are the same as what I am using.

I can't find a way to share this through a message, is there any way to send a .pbix?

Thanks! 

[v-jiascu-msft]

Hi @Anonymous,

You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.

Best Regards,

[Anonymous]

Hello @v-jiascu-msft,

Nothing is sensitive because this is all fabricated.  Below is the download link for the report:

link

Thank you!

[v-jiascu-msft]

Hi @Anonymous,

I don't have access to the file with my account. Could you check the sharing status please? Maybe you should share it outside your organization. 

Best Regards,

[Anonymous]

Hello @v-jiascu-msft,

I just wanted to update for anybody keeping up with the thread that the problem lied within the relationships between my tables. I had to delete one relationship and add a new one to make the filtering work correctly.

I appreciate your help!

[zedleb]

Hi @v-jiascu-msft; @Anonymous

I have a very similar issue.

I've a measure that evaluates the ranking (of a line of production based on the number of units of production that have passed via a series of workcenters). 

So BR RTG RANKING in the photo below is based on:

BR RTG RANK = 
RANKX (
    ALLSELECTED ( 'Pareto BR' );
    [BR UFs];
    ;
    DESC;
    DENSE
)

I would like to show a COUNT of how many lines are returned for any filter.

In the above example I'd have a count of 14 (which gives me a picture of how many combinations of production I have).

Any advice here would be very much appreciated.

The PROD BR ALTS is also a measure (combining individual rankings) to show the combined rank of each step.  A result of 1_1_1_1 is a route of production that uses the most popular workcenter at each step.  This could also be used to give a distinct count.

PROD BR ALTS = 
COMBINEVALUES (
    "_";
    [RTG RANK BR SA];
    [RTG RANK BR MA];
    [RTG RANK BR JA];
    [RTG RANK BR GA]
)

Cheers

zedleb

[zedleb]

I've resolved this by using a SUMMARIZED table, relating this to the fact table (it's a many to many but I set the filter from the fact table) and using the count in the visualisation. 

It works very well! 

[Anonymous]

Hi @v-jiascu-msft,

I can't upload a file from my organization to a place outside of the organization. Is there any way to send this .pbix file to you another way?