Forum Discussion

Szokens's avatar
Szokens
Helper II
1 year ago
Solved

Different column output basing on Parameter Selection

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 ...
dynamic values
formulas
parame
parameter
parameters
  • Szokens's avatar
    Szokens
    1 year ago

    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 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Szokens
Helper II
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.