Creating a List of Records Based on a Measure

[Matt0515]

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

[amitchandak]

@Matt0515 , Not very clear

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

[Matt0515]

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!

[Matt0515]

@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

[v-eachen-msft · 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"
)

[karun_r]

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