Different column output basing on Parameter Selection

[Szokens]

Hi Guys and Welcome Everyone! my first post in this community :).

I have following query. I have created parameter using "New Parameter" -> "Numeric Range"

Then I have edited parameter settings as follows:

Then I would like to have column with different output depending on parameter selection. I wrote formula as follows:

When I test it in report view, I always get "NOTPASSED", whatever I choose in the slicer. Do I miss anything or is there other workaround to achieve my goal?

[Szokens · Accepted solution]

Hi, my use case here required different solution. @Anonymous  thanks for this calculation I believe I will utilized it in the future. However here I got all values in one row which is not what I need.

Instead of this, I have created separate columns with calculations for day / week / month / quarter / year. Following this I have prepared separate vizuals with my required metric "Sends" (not mentioned before) on Y axis and on X axis I put my new column one by one separately for each vizual. Then I have used method described in following article to display my metric in different time-frame using buttons and bookmarks .

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive 

[Szokens]

I have tried both solutions and I am facing following issue when putting my calculation into measure (formula changed a bit following instructions given by @gaurav-lakhotia , but putting original claculation into measure gives the same error):

to make it works, I need to put each "sent_date" into ie. MAX() function for example but this doesn't give me required result - only one value (screenshot below) but my use case is to get list of all possible values...

What I would like to achieve here actually is changing dimension, as I will use MMD2 in colum of my future viz, so dimension will change when making selection in parameter.

[Anonymous]

Hi @Szokens ,

The measure will return summarize result, so when you only add the measure into the table, it will only give you an aggregated value. And you need to use SUM()/MAX()/MIN() to get data in measure.

Here I suggest you to add [sent_date] column in to the table visual as well. Then you table visual will contain two value, [sent_date] and [Parameter measure]. The measure will return results based on your column. If the measure logic is correct, you will get you want.

Best Regards,
Rico Zhou

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

[Szokens]

Hi @Anonymous thanks for suggestion, indeed when adding [send_date] as a dimension here, my measure is broken down and I see more than 1 result. Issue is that I see duplicates.

But my goal is to have after each selection in slicer ie "Monthly" distinct values in my table.

So for "Monthly" it will be two values "Mar'24" and "Apr'24".

For "Weekly" it will be two values "Week 13, 24" and "Week 14, 24".

It might be tuff to do it with "measure" field which need an aggregation.

[Anonymous]

Hi @Szokens,

I suggest you to try code as below to update your measure.

Parameter_TEST_MMD2_table_measure =
VAR _SUMMARIZE =
    SUMMARIZE (
        Merged_Prod_Query,
        [sent_date],
        "Dynamic",
            SWITCH (
                [SelectMeasure],
                1,
                    CONVERT ( MONTH ( 'Merged_Prod_Query'[sent_date] ), STRING ) & "/"
                        & CONVERT ( DAY ( Merged_Prod_Query[sent_date] ), STRING ) & "/"
                        & CONVERT ( YEAR ( 'Merged_Prod_Query'[sent_date] ), STRING ),
                2,
                    "Week " & CONVERT ( WEEKNUM ( Merged_Prod_Query[sent_date] ), STRING ) & ", "
                        & RIGHT ( CONVERT ( YEAR ( Merged_Prod_Query[sent_date] ), STRING ), 2 ),
                3,
                    LEFT ( CONVERT ( FORMAT ( Merged_Prod_Query[sent_date], "mmm" ), STRING ), 3 ) & " '"
                        & RIGHT ( CONVERT ( YEAR ( Merged_Prod_Query[sent_date] ), STRING ), 2 ),
                4,
                    "Q" & CONVERT ( QUARTER ( Merged_Prod_Query[sent_date] ), STRING ) & " '"
                        & RIGHT ( CONVERT ( YEAR ( Merged_Prod_Query[sent_date] ), STRING ), 2 ),
                "NOTPASSED"
            )
    )
VAR _DISTINCT =
    SUMMARIZE ( _SUMMARIZE, [Dynamic] )
RETURN
    CONCATENATEX ( _DISTINCT, [Dynamic], " , " )

Result is as below.

Best Regards,
Rico Zhou

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

[ValtteriN]

Hi,

The issue is that you are trying to use this as a column. If you place the dax into a measure your logic should work. I re-created this with test values:

Parameter = DATATABLE("a",STRING,{{"a"},{"b"}})

Measure 31 = SWITCH([Parameter Value],"a",1,3)

On a separate note what you are doing here doesn't really have anything to do with parameters. While you created the template for your calculated table and SELECTEDMEASURE calculation with it this is a separate topic.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[gaurav-lakhotia]

Hi @Szokens,

Instead of creating parameter, create table using Enter Data. Do not create any relationship for that table. Just put the column from Enter data table in a slicer. Apply business logic in a measure using SWITCH funtion.

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...