Dax - number of accounts that switched their codes more than twice in a year and magnitude of switch

[kalkhudary]

Hello, 

I am stumbled on a dax. I am looking into this scenario to resolve. I have Account Numbers, Rate Codes, YearMonth(Text date type). I am looking to get the number of accounts that switched their codes more than twice in a year. Then I also want to calculate the magnitude of code switching for each account. I have the distinct counts of rate categories in the values of a matrix table and when i filter it to look into>2, it does filter the "Different Rate Codes Used Column" or the Total Column instead of the numbers that is showing under the year. 

The intended output is to show i each year any rate codes that occurred more than twice in each of year 2021, 2022, 2023, 2024 only and filter out the ones that has number 1 or 2. Then, I need to measure the magnitutude of code switch for each account in the 4 years. Any ideas of how to dax it these 2 scenarios. Much appreciated.

[Anonymous]

Hi @kalkhudary ,

Can you show me what your original data structure looks like? Different data structures lead to completely different DAX.
If you want to filter out values ​​greater than 2 in this data, you need to use DAX as a filter:

But this also requires knowing your original data structure, thank you!

Best Regards,
Dino Tao

[kalkhudary]

@Anonymous My data is just one table and I am using it to geenrate measures. Dropping you here a sample of it with one account number sample that you can dax.

BKPERIOD	ACCOUNT NUMBER	RATE CATEGORY	Customer Code_2	BK Year
202408	211	PG-C-GT2	C	2024
202409	211	PG-C-GT2	C	2024
202407	211	PG-C-GT2	C	2024
202409	211	PG-C-GS2	C	2024
202408	211	PG-C-GS2	C	2024
202406	211	PG-C-GT1	C	2024
202407	211	PG-C-GS2	C	2024
202406	211	PG-C-GS1	C	2024
202404	211	PG-C-GT1	C	2024
202403	211	PG-C-GT1	C	2024
202405	211	PG-C-GT1	C	2024
202404	211	PG-C-GS1	C	2024
202403	211	PG-C-GS1	C	2024
202405	211	PG-C-GS1	C	2024
202402	211	PG-C-GT1	C	2024
202401	211	PG-C-GT1	C	2024
202402	211	PG-C-GS1	C	2024
202401	211	PG-C-GS1	C	2024
202312	211	PG-C-GT1	C	2023
202311	211	PG-C-GT1	C	2023
202312	211	PG-C-GS1	C	2023
202311	211	PG-C-GS1	C	2023
202310	211	PG-C-GT1	C	2023
202309	211	PG-C-GT1	C	2023
202309	211	PG-C-GS1	C	2023
202310	211	PG-C-GS1	C	2023
202307	211	PG-C-GT1	C	2023
202308	211	PG-C-GT1	C	2023
202306	211	PG-C-GS2	C	2023
202308	211	PG-C-GS1	C	2023
202307	211	PG-C-GS1	C	2023
202305	211	PG-C-GT2	C	2023
202306	211	PG-C-GT2	C	2023
202304	211	PG-C-GS2	C	2023
202305	211	PG-C-GS2	C	2023
202303	211	PG-C-GT2	C	2023
202304	211	PG-C-GT2	C	2023
202302	211	PG-C-GT2	C	2023
202302	211	PG-C-GS2	C	2023
202303	211	PG-C-GS2	C	2023
202301	211	PG-C-GT2	C	2023
202212	211	PG-C-GT2	C	2022
202212	211	PG-C-GS2	C	2022
202301	211	PG-C-GS2	C	2023
202210	211	PG-C-GT2	C	2022
202211	211	PG-C-GT2	C	2022
202211	211	PG-C-GS2	C	2022
202210	211	PG-C-GS2	C	2022
202209	211	PG-C-GT2	C	2022
202208	211	PG-C-GT2	C	2022
202207	211	PG-C-GT2	C	2022
202209	211	PG-C-GS2	C	2022
202208	211	PG-C-GS2	C	2022
202207	211	PG-C-GS2	C	2022
202206	211	PG-C-GT2	C	2022
202206	211	PG-C-GS2	C	2022
202205	211	PG-C-GT2	C	2022
202204	211	PG-C-GS2	C	2022
202204	211	PG-C-GS2	C	2022
202205	211	PG-C-GS2	C	2022

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[kalkhudary]

@asasasas Based on data above and as explained in the prompt, I need a dax that looks into the total number of repeated codes in each year where I can use as a filter to show accounts that has 3 repeated codes only for example and blanking the others that are less than 3.

Step1 Result:

Account Number	2021	2022	2023	2024
221	0	2	3	4

Step 2 Result After Filter:

Account Number	2021	2022	2023	2024
221			3	4

[Anonymous · Accepted solution]

Hi @kalkhudary ,

Due to the limitations of the matrix itself, it may not be feasible to implement filtering of Value values in the matrix with your data. You would need a measure or calculated column to calculate the number of repeated codes and put them into the Value of the matrix, but then you would not be able to filter the values of individual cells in the matrix.
My suggestion is that you either switch to table visual objects:

Codes = DISTINCTCOUNT(YourTable[RATE CATEGORY])

And use this DAX as a filter:

Filter = 
IF(
    [Codes] < 3,
    0,
    1
)

Either that or just skip this step:

Directly display the results after filtering:

repeated codes = 
VAR _Count = DISTINCTCOUNT(YourTable[RATE CATEGORY])
RETURN
IF(
    _Count < 3,
    BLANK(),
    _Count
)

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.