CODEWITHSUNDEEP
stringdaxgroup
Srinivas Dhulipala
Srinivas Dhulipala

Reputation: 1

comparison between two strings for a group of unsorted values

My SQL returns 2 columns with concatenated strings, I split both the columns in power query and I want to use DAX to do a comparison on the values in both the columns for every ID column. The measure should show the return the distinct count of ColumnA when Agency = Yes and when either of the mismatch conditions are met:

  1. either Region A and Region B is null for every ColumnA and date
  2. Either of the regions have a mismatch value for every ColumnA and date
  3. Either one of the Regions is null and the other has value for every ColumnA and date. From the below example, For 11/1/2024, the total should be 2(123 and 567 are mismatch, 345 is not counted as Agency = No),for 11/8/2024 the total will be 1(345 is match) The measure for the whole population should return a count of 2 (123, 345).

Please let me know the DAX that is needed for this situation. In SQL i used String_split to compare the values

Date ColumnA Agency RegionA RegionB
11/01/2024 123 Yes Phoenix Phoenix
11/01/2024 123 Yes Charlotte
11/08/2024 123 Yes Phoenix Phoenix
11/08/2024 123 Yes Charlotte Charlotte
11/01/2024 345 No Edison
11/01/2024 345 No Atlanta
11/01/2024 567 Yes Tampa
11/01/2024 567 Yes Raleigh
11/01/2024 567 Yes Memphis
11/08/2024 345 Yes Atlanta Edison
11/08/2024 345 Yes Edison
11/08/2024 567 Yes Tampa Tampa
11/08/2024 567 Yes Raleigh Raleigh

Wrote the query in SQL but user wants a drillthrough of the details that make the counts so trying to achieve the result with DAX

Upvotes: -1

Views: 34

Answers (1)

Michal
Michal

Reputation: 6064

Assuming I correctly understand your requirements:

enter image description here

Mismatch Count = 
VAR FilteredData =
    FILTER (
        'Table',
        'Table'[Agency] = "Yes"
            && (
                -- Condition 1: Either RegionA or RegionB is NULL
                ISBLANK('Table'[RegionA]) || ISBLANK('Table'[RegionB])
                ||
                -- Condition 2: Mismatch between RegionA and RegionB
                'Table'[RegionA] <> 'Table'[RegionB]
            )
    )
VAR DistinctIDs = DISTINCT ( SELECTCOLUMNS ( FilteredData, "DistinctColumnA", 'Table'[ColumnA] ) )
RETURN
COUNTROWS ( DistinctIDs )

Upvotes: 0

Related Questions