Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Is there a way to build a "True" / "False" indicator that is driven off information across rows instead of columns? For instance, I want to identify clients (unique ID in a field called "Client ID") within a certain mail month (stored as 1, 2, 3, 4, etc. in a field called "Start Month") that have a "Test" value in a field called "Version Type".
I included this screenshot which I hope helps. For Client ID 102023472 in the month of February (2) there is a row that contains "Test" in the field called "Version Type". So in this case I would want all corresponding rows associated with Client ID 102023472 in the month of February (2) marked with "True". However, for Client ID's 102023487 and 102023489 in the month of February (2), they do not have a row that contains "Test" in the field called "Version Type". So in this case I would want all corresponding rows associated with Client ID's 102023487 and 102023489 in the month of February (2) marked with "False".
Solved! Go to Solution.
Hi @krichmond ,
Please try this calculated column:
True_False =
VAR _ifexistst =
SUMMARIZE (
FILTER (
mv_perfex,
[Start Month] = EARLIER ( mv_perfex[Start Month] )
&& [clientid] = EARLIER ( mv_perfex[clientid] )
),
[versiontype]
)
RETURN
IF ( "TEST" IN _ifexistst, TRUE (), FALSE () )
In what form would you like to find data with "Version_type" as "TEST"? You can create a measure and format it conditionally.
Color = IF(MAX('mv_perfex'[versiontype])="TEST","red","black")
Alternatively, you can place the [version_type] field on a slicer to filter the visual, or you can create a measure to place as shown in the following image.
Flag = IF( MAX('mv_perfex'[versiontype])="TEST",1,0)
If you have more detailed requirements for displaying "TEST", please mark the answer to this post as a solution and republish a post detailing your needs, thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @krichmond ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a calculated column.
True_False =
var _ifexistst=SUMMARIZE(FILTER(mv_perfex,[versiontype]="TEST"),[clientid])
return IF([clientid] in _ifexistst,TRUE(),FALSE())
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-tangjie-msft - This is close but not 100% yet. I think additional criteria needs to be added into the mix above and beyond just Client ID. As you can see from my screenshot, the formula you provided puts TRUE next to everything associated with that Client ID.
What I am trying to do is find instances where a Client ID for a certain Start Month has a row with "Test" under the "Version Type" field. When it finds this, it flags the other items with the same Client ID and Start Month as "Test" as well.
Hi @krichmond ,
Please try this calculated column:
True_False =
VAR _ifexistst =
SUMMARIZE (
FILTER (
mv_perfex,
[Start Month] = EARLIER ( mv_perfex[Start Month] )
&& [clientid] = EARLIER ( mv_perfex[clientid] )
),
[versiontype]
)
RETURN
IF ( "TEST" IN _ifexistst, TRUE (), FALSE () )
In what form would you like to find data with "Version_type" as "TEST"? You can create a measure and format it conditionally.
Color = IF(MAX('mv_perfex'[versiontype])="TEST","red","black")
Alternatively, you can place the [version_type] field on a slicer to filter the visual, or you can create a measure to place as shown in the following image.
Flag = IF( MAX('mv_perfex'[versiontype])="TEST",1,0)
If you have more detailed requirements for displaying "TEST", please mark the answer to this post as a solution and republish a post detailing your needs, thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@krichmond Try:
Measure =
VAR __Client = MAX('Table'[Client ID])
VAR __StartMonth = MAX('Table'[Start Month])
VAR __Table = SELECTCOLUMNS(FILTER(ALL('Table'),[Client ID] = __Client && [Start Month] = __StartMonth),"Version Type",[Version Type])
VAR __Result = IF("TEST" IN __Table,TRUE(),FALSE())
RETURN
__Result
@Greg_Deckler - I tried that DAX and it didn't return the expected results. "mv_perfex" is the table name so I switched out your "Table" references with that. But outside of that, I didn't adjust anything.
@krichmond Can you post sample data as text?
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |