Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to make a formula that uses data from different colums within the same table.
Status Taak = IF(AND(Taken[PercentageVoltooidTaak]<>100;Taken[EinddatumTaak]<TODAY());"Te laat";IF(Taken[PercentageVoltooidTaak]=100;"Gesloten";IF(Taken[PercentageVoltooidTaak]<>100;Taken[EinddatumTaak]>TODAY();"Open")))
The Tasks have a Start and an End date and when they are done the task gets 100% so what i'm trying to do here is the same as in Excel but in Dax its a bit different and gives this error
Expressions that yield variant data-type cannot be used to define calculated columns.
Can someone tell me what im doing wrong ?
Solved! Go to Solution.
Sorry to beat a dead horse, but considering statements are evaluated in order and evalutation stops at first TRUE() statement, you could shorten it up to this:
Status Taak = SWITCH ( TRUE (); Taken[PercentageVoltooidTaak] = 100; "Gesloten"; Taken[EinddatumTaak] < TODAY (); "Te laat"; Taken[EinddatumTaak] > TODAY (); "Open"; "Other" )
The problem is you missed the "AND" function on the innermost IF statement. Without it Dax detected the possibility your innermost IF could return a boolean data type along with the other text data types. All values in a column have to be of the same datatype so it threw an error. Excel has the "variant" data type which is not allowed in calculated columns.
I think you intended this:
Status Taak = IF ( AND ( Taken[PercentageVoltooidTaak] <> 100; Taken[EinddatumTaak] < TODAY () ); "Te laat"; IF ( Taken[PercentageVoltooidTaak] = 100; "Gesloten"; IF ( AND ( Taken[PercentageVoltooidTaak] <> 100; Taken[EinddatumTaak] > TODAY () ); "Open" ) ) )
FWIW, I agree with @Phil_Seamark that his way is cleaner and easier to read. Internally it gets rewritten as nested IF statements, but for you easier to deal with.
Hi @RvdHeijden
Please try this approach. You may need to tweak the rules but I hope you get the idea
Status Taak = SWITCH( --- IF TRUE -- True(), -- Tests ---- Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]<TODAY() ,"Te laat", Taken[PercentageVoltooidTaak]=100 ,"Gesloten", Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]>TODAY(), "Open", -- ELSE ---- "Other" )
@Phil_Seamark thanks for your help but we are not quite there yet 🙂
Status Taak = SWITCH(Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]<TODAY() ;"Te laat"; Taken[PercentageVoltooidTaak]=100 ;"Gesloten"; Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]>TODAY(); "Open";"Other" )
The formula still returns this error:
Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
You missed the TRUE() statement @Phil_Seamark showed.
Status Taak = SWITCH ( TRUE (); Taken[PercentageVoltooidTaak] <> 100 && Taken[EinddatumTaak] < TODAY (); "Te laat"; Taken[PercentageVoltooidTaak] = 100; "Gesloten"; Taken[PercentageVoltooidTaak] <> 100 && Taken[EinddatumTaak] > TODAY (); "Open"; "Other" )
Sorry to beat a dead horse, but considering statements are evaluated in order and evalutation stops at first TRUE() statement, you could shorten it up to this:
Status Taak = SWITCH ( TRUE (); Taken[PercentageVoltooidTaak] = 100; "Gesloten"; Taken[EinddatumTaak] < TODAY (); "Te laat"; Taken[EinddatumTaak] > TODAY (); "Open"; "Other" )
Hi @RvdHeijden,
Great to hear the problem got resolved! Could you accept helpful replies as solution to close this thread?
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |