How to create Boolean Flag based on selected slicer value ?

[cruncher]

Hi Experts,

This is the sample data of my table.

Region	Vertical	Business Unit	Quarter	Sales	Current Quarter Flag	Previous Quarter Flag
East	Vert_1	BU1	Q3 24	3529	0	1
East	Vert_1	BU2	Q3 24	3702	0	1
East	Vert_1	BU3	Q3 24	3182	0	1
East	Vert_2	BU1	Q3 24	4934	0	1
East	Vert_3	BU2	Q3 24	1257	0	1
West	Vert_1	BU1	Q3 24	545	0	1
West	Vert_1	BU2	Q3 24	3602	0	1
West	Vert_1	BU3	Q3 24	2233	0	1
West	Vert_2	BU1	Q3 24	3485	0	1
West	Vert_3	BU2	Q3 24	1995	0	1
East	Vert_1	BU1	Q4 24	7392	1	0
East	Vert_1	BU2	Q4 24	8531	1	0
East	Vert_1	BU3	Q4 24	3379	1	0
East	Vert_2	BU1	Q4 24	1462	1	0
East	Vert_3	BU2	Q4 24	5004	1	0
West	Vert_1	BU1	Q4 24	2872	1	0
West	Vert_1	BU2	Q4 24	255	1	0
West	Vert_1	BU3	Q4 24	5231	1	0
West	Vert_2	BU1	Q4 24	9632	1	0
West	Vert_3	BU2	Q4 24	7786	1	0

I want to create an card visual to show the sales based on an Drop Down parameter named Selected quarter.

1. If Selected value of parameter is Current Quarter then I want to sum all the sales where current quarter flag =1 or Multiple the sales with Current quarter flag which also return the same thing.

2. If Selected value of parameter is Previous Quarter then I want to sum all the sales where previous quarter flag =1 or Multiple the sales with Previous quarter flag which also return the same thing.

How to achieve this. I have an idea this can be achieved using Parameter table but not sure how to implement this.

Please help.

Thanks

[bhanu_gautam · Accepted solution]

@cruncher , Try using below steps

Create a new table in Power BI for the parameter selection. This table will have two values: "Current Quarter" and "Previous Quarter".

Add a slicer to your report using the ParameterTable to allow users to select between "Current Quarter" and "Previous Quarter".

Create a measure that calculates the sales based on the selected parameter. This measure will check the selected value and sum the sales accordingly.

SelectedSales =
VAR SelectedParameter = SELECTEDVALUE(ParameterTable[Parameter])
RETURN
SWITCH(
SelectedParameter,
"Current Quarter", CALCULATE(SUM('YourTable'[Sales]), 'YourTable'[Current Quarter Flag] = 1),
"Previous Quarter", CALCULATE(SUM('YourTable'[Sales]), 'YourTable'[Previous Quarter Flag] = 1),

Create a Card Visual:
Add a card visual to your report and set the value to the SelectedSales measure. This will display the total sales based on the selected quarter from the slicer.

[DataNinja777]

Hi @cruncher ,

 

In this case, I recommend using the calendar table as a dimension table and establishing a relationship between the calendar table and the fact table. This setup allows you to take advantage of standard DAX time intelligence functions, including Quarter-to-Date and Previous Quarters. This approach is simpler, requires less maintenance, and provides greater flexibility.

Best regards,

[cruncher]

Thanks @DataNinja777 for the suggestion. We have a calender table in db and these flags are coming from that table only. I want to avoid the calculation at PowerBI level which I believe will slow down the loading time of view.

[bhanu_gautam · Accepted solution]

@cruncher , Try using below steps

Create a new table in Power BI for the parameter selection. This table will have two values: "Current Quarter" and "Previous Quarter".

Add a slicer to your report using the ParameterTable to allow users to select between "Current Quarter" and "Previous Quarter".

Create a measure that calculates the sales based on the selected parameter. This measure will check the selected value and sum the sales accordingly.

SelectedSales =
VAR SelectedParameter = SELECTEDVALUE(ParameterTable[Parameter])
RETURN
SWITCH(
SelectedParameter,
"Current Quarter", CALCULATE(SUM('YourTable'[Sales]), 'YourTable'[Current Quarter Flag] = 1),
"Previous Quarter", CALCULATE(SUM('YourTable'[Sales]), 'YourTable'[Previous Quarter Flag] = 1),

Create a Card Visual:
Add a card visual to your report and set the value to the SelectedSales measure. This will display the total sales based on the selected quarter from the slicer.

[cruncher]

Thanks @bhanu_gautam for the alternate solution. After a short struggle, I was able to achieve this using below calc

SelectedSales =
VAR SelectedParameter = SELECTEDVALUE(ParameterTable[Parameter])
RETURN
SWITCH(
SelectedParameter,
"Current Quarter",SUMX('YourTable','YourTable'[Sales]*'YourTable'[Current Quarter Flag]),
"Previous Quarter",SUMX('YourTable','YourTable'[Sales]*'YourTable'[Previous Quarter Flag])
)

Which approach (SUMX vs Calculate) is more optimized as per your experience if we have 25 Million rows in a table.

[cruncher]

In this similar context, I am facing another problem. We have a source column in table that contains different values like Sales,Discount,Forecast.

I have a created an source parameter using parameter table similar approach.

How can i create an Boolean flag based on Selected value in parameter so that when Sales in selected, It will filter True for all rows from table where Source=Sales and False for where source <> Sales.

This Boolean flag should be dynamic so that visuals are filtered dynamically as per slicer selection. Should I have to create an calculated column or Measure for same ? How to decide when to create calculated column vs Measure ?

I am thinking to put that Boolean flag in filters so that all measures related to sales are filtered in one go

[Anonymous · Accepted solution]

Hi @cruncher ,

About:

Which approach (SUMX vs Calculate) is more optimized as per your experience if we have 25 Million rows in a table.

For 25 million rows the performance is almost the same.

And here is my sample data:

Here is the parameter table:

You can use this DAX to create a measure:

IsSelectedSource = 
VAR SelectedParam = SELECTEDVALUE(ParameterTable[Source])
RETURN
IF (
    ISFILTERED(ParameterTable[Source]),
    IF(
        MAX('Table'[Source]) = SelectedParam,
        TRUE(),
        FALSE()
    ),
    TRUE()
)

And the final output is as below:

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

[cruncher]

Thank you @Anonymous . It works as expected