topic Re: Compare data type text columns with measure of single value and if match is exact then 1 else zero in DAX Commands and Tips

Compare data type text columns with measure of single value and if match is exact then 1 else zero

Anonymous — Wed, 10 Jul 2024 09:13:00 GMT

Hi Power BI Team,

I trying dax query to compare data type text column in physical table with measure(result is single value) and only if the particular row is exact match of measure, then result should be 1 else 0

Below is formula for Scorecard Concatenate which is populating based on slicer

Scorecard_Concanteate =

var Division = [DivisionFiltersSelectedInvoicing]

var GEO = [RBUFiltersSelectedInvoicing]

var IRIS = [CustomerFiltersSelectedInvoicing]

var Ind = [INDFiltersSelectedInvoicing]

var Branch = [BranchFiltersSelectedInvoicing]

var CTRY = [CountryFiltersSelectedInvoicing]

RETURN Division & GEO & CTRY & IRIS & Ind & Branch and below is dax for Scorecard filter

Scorecard Filter =

CALCULATE(

[Result],

FILTER('DWH FACT_O2C_Scorecard_LIVEDATA', 'DWH FACT_O2C_Scorecard_LIVEDATA'[Concatenate_1] = [Scorecard_Concanteate]

))

Where Result = 1.. Please assist

Re: Compare data type text columns with measure of single value and if match is exact then 1 else zero

BeaBF — Wed, 10 Jul 2024 10:41:44 GMT

Anonymous Hi! Can you paste sample data? Thx

Re: Compare data type text columns with measure of single value and if match is exact then 1 else zero

Shaurya — Wed, 10 Jul 2024 11:58:07 GMT

Hi Anonymous,

Try using this:

Scorecard_Concatenate = VAR Division = [DivisionFiltersSelectedInvoicing] VAR GEO = [RBUFiltersSelectedInvoicing] VAR IRIS = [CustomerFiltersSelectedInvoicing] VAR Ind = [INDFiltersSelectedInvoicing] VAR Branch = [BranchFiltersSelectedInvoicing] VAR CTRY = [CountryFiltersSelectedInvoicing] RETURN Division & GEO & CTRY & IRIS & Ind & Branch

Scorecard Filter = CALCULATE( 1, FILTER( 'DWH FACT_O2C_Scorecard_LIVEDATA', 'DWH FACT_O2C_Scorecard_LIVEDATA'[Concatenate_1] = [Scorecard_Concatenate] ) )

Did I answer your question? Mark this post as a solution if I did!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

Re: Compare data type text columns with measure of single value and if match is exact then 1 else zero

Anonymous — Thu, 11 Jul 2024 07:58:38 GMT

Hi Shaurya,

Thanks for trying, when i use your formula below result appears

For All rows 1 is appearing but only row in Red should be 1 and rest zero

BeaBF Apologise can't share sample file do to secruity issue.

Re: Compare data type text columns with measure of single value and if match is exact then 1 else zero

Anonymous — Tue, 16 Jul 2024 06:01:37 GMT

Hi,Anonymous.I am glad to help you.
Hello, Shaurya ,BeaBF,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

According to your description, you want to compare the result of an ordinary column [Concatenate_1] with the value of multiple measure for the value of splicing the measure, if the two values are equal to return 1 otherwise return 0.
I see that you provide two measure, the following I will analyze the code you provide, if my analysis is correct, you can refer to my suggestions below, if my understanding is wrong, please correct and provide the correct non-sensitive test data or screenshots, thank you.
1.
For the following code you provided, I think it is a measure, and the var variables are all measure values, because first of all you provided Scorecard_Concanteate itself is a measure, and for the measure if you directly reference the columns you need to use the aggregation function, but according to the code does not use any aggregation function for the var variables again. But according to the code, there is no aggregation function used for var variables, such as MAX function

Scorecard_Concanteate = var Division = [DivisionFiltersSelectedInvoicing] var GEO = [RBUFiltersSelectedInvoicing] var IRIS = [CustomerFiltersSelectedInvoicing] var Ind = [INDFiltersSelectedInvoicing] var Branch = [BranchFiltersSelectedInvoicing] var CTRY = [CountryFiltersSelectedInvoicing] RETURN Division & GEO & CTRY & IRIS & Ind & Branch

2. For your problem: the final result always returns 1

Scorecard Filter = CALCULATE( [Result], FILTER( 'DWH FACT_O2C_Scorecard_LIVEDATA', 'DWH FACT_O2C_Scorecard_LIVEDATA'[Concatenate_1] = [Scorecard_Concanteate] ) )

The measure Scorecard Filter calculates the [Result] but only for the rows in the ‘DWH FACT_O2C_Scorecard_LIVEDATA’ table where the Concatenate_1 column equals the [Scorecard_Concanteate] measure.

In fact, the final result of this measure is calculated based on the value of [Result], so if the value of measure[Result] is always 1 in the current context, then the Scorecard Filter will always calculate 1 for each row.
Here is my test.
When the value of [Result] is dynamic in the current context, the final result of the Scorecard Filter is also dynamic.

However, if I set the value of [Result] to a constant value of 1 (which is always 1 in the simulation environment), the final result of the Scorecard Filter will also be 1 for each row.

Here is my suggestion.
I suggest using the IF function directly to make a judgment that the current row's [Concatenate_1] and the current row's MEASURE [Scorecard_Concanteate]
Compare, if equal, return 1, otherwise return 0
The current row's value can be retrieved by using the aggregate function in the measure
like this:

M_Scorecard Filter = VAR value01=MAX('DWH FACT_O2C_Scorecard_LIVEDATA'[Concatenate_1]) RETURN IF(value01=[Scorecard_Concanteate], 1,0)

Of course, if your original [Result] itself carries complex filters and calculations, and you want to continue to use the original code, I recommend that you double-check the results of the [Result] calculations, you can show the value of [Result] separately, to see if the value of this measure is the result you expected, and whether its value is changing in the current calculation environment, or always return a constant value of 1 (including the filters inside the code and outside the slicer/filter area/visual itself, which affect the value of measure[result])

I hope my test will bring you good ideas, and I wish you a speedy solution to your problem!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.