Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Counting a measure return

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!

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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]
)

Counting-a-measure-return

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-jiascu-msft
Employee
Employee

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]
)

Counting-a-measure-return

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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!

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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! 

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello @v-jiascu-msft,

 

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

 

link

 

Thank you!

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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!

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
)

 

 

CM1.PNG

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
Frequent Visitor

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! Smiley Happy

Anonymous
Not applicable

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?

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.