cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matt0515
Frequent Visitor

Creating a List of Records Based on a Measure

Hello all, and thanks in advance for any feedback on the following question.

 

I have a rather complex measure that I would like to not simply count the rows that qualify, but rather have the ability to display the rows information (columns) in a list as well.

 

Is there a way I can structure a measure to do this?

 

example of my current measure - set up to return the count (sum of an index column) of those that qualify.

 

Total 2020 MT SQL =
CALCULATE (
    SUM(Current_Report_Raw_Data[Index])+0,
    FILTER (
        ALLSELECTED ( Current_Report_Raw_Data ),
            YEAR ( Current_Report_Raw_Data[SQL Date] ) = 2020
            &&          ( Current_Report_Raw_Data[Opportunity Solution] = "MT")
            &&          ( Current_Report_Raw_Data[Sirius Stage] = "X")
            &&          ( Current_Report_Raw_Data[Opportunity Record Type] = "A" || Current_Report_Raw_Data[Opportunity Record Type] = "B")
            &&          ( Current_Report_Raw_Data[Crystallised Opp Booking Value (GBP)] > 1
            &&          ( Current_Report_Raw_Data[Crystallised Opp Booking Value (GBP)] > 5000 || NOT Current_Report_Raw_Data[Opportunity Sale Type] = "C")
            && NOT      ( Current_Report_Raw_Data[Enterprise Sales Team] ) = "D"
            && NOT      ( Current_Report_Raw_Data[Account Name] = "E" || Current_Report_Raw_Data[Account Name] = "F")
            && NOT      ( Current_Report_Raw_Data[Main Product] = "G" || Current_Report_Raw_Data[Main Product] = "H")
            && NOT      ( Current_Report_Raw_Data[Reason for Closing] = "I")
            && NOT      ( Current_Report_Raw_Data[Opportunity Sale Type] ) = "J"

    )
))
 
Thanks again for any help!
 
- Matt
1 ACCEPTED SOLUTION

Hi @Matt0515 ,

 

According to your example, you could try SWITCH() function.

SWITCH (
    TRUE (),
    AND (
        OR ( 'Table'[Column X] = "a", 'Table'[Column X] = "b" ),
        'Table'[Column Y] = "xyz"
    ), "Business Unit 1",
    AND (
        OR ( 'Table'[Column X] = "c", 'Table'[Column X] = "d" ),
        'Table'[Column Y] = "abc"
    ), "Business Unit 2"
)

 

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@Matt0515 , Not very clear

Your condition seems mutually exclusive.  So the option is that you create a column or keep the same measure.

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak, sorry not very clear.

 

The condition is mutually exclusive.  How can I create a column in my dataset based on my measure?  I imagine that is done in the Query Editor?

 

Thanks!

@amitchandakAllow me to rephrase:

 

I have a dataset where I need to identify what business unit each row belongs to.

 

the definition of each business unit is diffirent than the next.

 

I would like to create a custom column that will result in the business unit for each row.

 

The example above would define a specific business unit and the paramiters would change for the next business unit ( and so on )

 

Where I am struggling is how to structure the Custom Column code to accept multiple paramiters to achieve a single column with multiple results.

 

example:

 

 IF Column X = "a" OR "b", AND Column Y = "xyz" THEN "Business Unit 1"

OR / ELSEIF

IF Column X = "c" OR "d", AND Column Y = "abc" THEN "Business Unit 2"

 

Hopefully this makes more sense.  I'm ultimatly attempting to make a single column of multiple resuts based on rules that identify exclusive rows.

 

Thanks

 

 

Hi @Matt0515 ,

 

According to your example, you could try SWITCH() function.

SWITCH (
    TRUE (),
    AND (
        OR ( 'Table'[Column X] = "a", 'Table'[Column X] = "b" ),
        'Table'[Column Y] = "xyz"
    ), "Business Unit 1",
    AND (
        OR ( 'Table'[Column X] = "c", 'Table'[Column X] = "d" ),
        'Table'[Column Y] = "abc"
    ), "Business Unit 2"
)

 

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

View solution in original post

karun_r
Microsoft
Microsoft

Can you share a sample output that you have right now and the sample that you would like to have ?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.