IF Measure using text fields

DAX DAX Calculations
1

HI,

Can someone tell me how i can perform this IF or Switch Statement as a measure? I dont want to use a calculated column or in Query editor i specifically need a measure is this possible?

I know the Dax isnt correct what i wrote here but to give you the example of what i want to do.

Basically i want to know if the staff member is meant to be on that program or not by comparing 2 text fields and matching them against another 2 text fields and returning the measure column yes or no.

Supporting Yes Or No = IF( 
Project number_1 = Project Number && Project Description_2 = Project Description ),
 "Yes", "No" )

Capture
Capture1264Ă—683 162 KB

EDNA SWitch or IF.pbix
(746.8 KB)

Thanks

Dan

2

Hello @Krays23,

Thank You for posting your query onto the Forum.

Is this the type of result you’re looking for? Below is the screenshot provided for the reference -

image
image1205Ă—598 90.5 KB

Here’s the formula that has been used -

Supporting Yes Or No = 
IF( 
    SELECTEDVALUE( 'EDNA Switch Test'[Project Number_1] ) = SELECTEDVALUE( 'EDNA Switch Test'[Project Number] ) && 
    SELECTEDVALUE( 'EDNA Switch Test'[Project Description_2] ) = SELECTEDVALUE( 'EDNA Switch Test'[Project Description] ) ,
     "Yes", "No" )

Also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

EDNA SWitch or IF.pbix (746.1 KB)

1 Like
3

@Krays23,

@Harsh beat me to it, but I’ll post my solution as well for the sake of completeness.

Switch Test =

VAR ProjNumTest =
    IF(
        EXACT(
            SELECTEDVALUE( 'EDNA Switch Test'[Project Number_1] ),
            SELECTEDVALUE( 'EDNA Switch Test'[Project Number] )
        ),
        1,
        0
    )
VAR ProjDescTest =
    IF(
        EXACT(
            SELECTEDVALUE( 'EDNA Switch Test'[Project Description_2] ),
            SELECTEDVALUE( 'EDNA Switch Test'[Project Description] )
        ),
        1,
        0
    )
VAR CombinedAND = ProjNumTest * ProjDescTest
RETURN
    IF( CombinedAND > 0, "SAME", BLANK() )

Note that EXACT is case-sensitive in terms of defining a match. If you want to relax that assumption, you can use SEARCH instead.

This solution uses the “dummy variable” logic from my video last week. It might be overkill for this simple AND case, but if the condition to define a “match” gets any more complex, I think you’ll find it very useful.

I hope this is helpful. Solution file attached.

1 Like
4

Thanks guys for speedy response and the 2 solutions the model is a little more complex than I posted so I’ll give it a go and see how I get on with your logic and may be back in touch

Thanks

Dan

2 Likes
5

Hello @Krays23,

You’re Welcome. :slightly_smiling_face:

Sure, we would be happy to hear back from you about how it goes.

Thanks and Warm Regards,
Harsh

1 Like
6

HI @BrianJ

I have tried your measure but im getting errors.

I cannot open your PBIX file as im using an older version it seems

Capture
Capture1194Ă—608 64.4 KB

7

Hi @Harsh

As suspected im back lol.

So in simple terms as you showed me your measure works well when i apply it to my model it goes crazy.

I get a row for every date for ever staff member and also i get a project description entry for every type of project description in the materials & projects table.

Capture
Capture1240Ă—638 127 KB

The measure actually works theres yes and no in the right place just there something off with the context or relationships probably.

if you remove your measure from the table thats how i need it with the yes and no in place as shown here.

Capture
Capture1234Ă—633 131 KB

Ive posted the full model below

Regards and thanks

Dan

EDNA SWitch or IF.pbix (1020.9 KB)

8

Hello @Krays23,

There is a data model problem. You’ve designed your model incorrectly and than using and dropping the the fields from all over the table into the visualization. So by default it will give you the incorrect results. The formula is not with the formula.

And this is the reason why you’re getting the results as you suggested - “I get a row for every date for ever staff member and also i get a project description entry for every type of project description in the materials & projects table.”

Firstly, this is how your data model looks like -

Data Model Status
Data Model Status589Ă—522 13.9 KB

Now, in your visualization you’ve used the following fields -

  1. Date. (This comes from the Date Table which is given the status of Dimension Table)

  2. Employee Number & Name. (This comes from the Head Count Table which is given the status of Dimension Table)

  3. Project Number. (This again comes from the Head Count Table which is given the status of Dimension Table)

  4. Project Description. (This again comes from the Head Count Table which is given the status of Dimension Table)

  5. Area. (This again comes from the Head Count Table which is given the status of Dimension Table)

  6. Project Number. (This comes from the Materials & Projects Table which is given the status of Dimension Table)

  7. Project Description. (This comes from the Materials & Projects Table which is given the status of Dimension Table)

Now, how can you expect a result when you’re using all the different fields from the dimension table and putting them together in one visualization (table visual) where there’s no relationship between them in the first place.

Firstly, I sincerely recommend you to please organize/design the data model properly by de-normalizing the tables. Your dimnsion tables itself contains 10+ columns which is absolutely never recommended. Please go through the “Advanced Data Transformations and Modelling Course” and design your model properly. Below is the link of the course provided for the reference.

Thanks and Warm Regards,
Harsh

9

@Krays23,

The measure code I provided works fine – it looks like what happened is you missed copying the line above the variable definition, which was

Switch Test =

If you add that back into your measure code, you should be good to go.

  • Brian
10

Ok thanks @Harsh

So all the fields I’m using in the visualisation need to be in the main fact table on the bottom row SAP SQ00 confirmations and not from filter tables at the top right

By dimension tables are you meaning filter tables on the top row.

I have watched the video but obviously need to go over it again. Sorry for the naivety I thought I was doing well maybe not

Thanks

Dan

11

Hello @Krays23,

“So all the fields I’m using in the visualisation need to be in the main fact table SAP SQ00 confirmations and not from filter tables right”.

I’m not sure how will you put all the fields in your Fact Table and secondly if you put all the fields into the Main Fact Table “SAPSQ00 confirmations” it will bloat the size of the model. You need to identify which are your fact table and which are the dimensions. As of now, your dimension table “Head Count” is containing the information like it contains in the fact table.

I’m providing a link of an article below which will help to understand what’s the difference between Fact and Dimension tables.

Dimension Tables acts as filter tables but not all filter tables are dimension tables. For example, you may create a Disconnected Secondary Table to filter the analysis which may not be linked with the Fact Table as well.

Also as recommended please go through “Data Modelling” Course which will help you understand the concepts much more better.

Hoping this is useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

12

HI @Harsh,

Ive been through the information you sent me and im struggling to understand a few things.

You Said

Now, how can you expect a result when you’re using all the different fields from the dimension table and putting them together in one visualization (table visual) where there’s no relationship between them in the first place.

I do have relationships between them for head count its employee number and Materials its material number and Dates is start execution date.

Your dimnsion tables itself contains 10+ columns which is absolutely never recommended. Please go through the “ Advanced Data Transformations and Modelling Course ” and design your model properly. Below is the link of the course provided for the reference.

In the Radacad document they suggest dimension tables should be wide ?

## Dimension Tables are Wide

Another golden rule in designing dimension tables is to make them wide. Add fields that are related to that, more fields in the dimension table means more power for slicing and dicing the data. If you have just three fields of Customer first name, last name, and gender, then it means you can only slice and dice data by these three. However, if you have a customer table with all columns below, then you will have much more power in slicing and dicing;

As of now, your dimension table “ Head Count ” is containing the information like it contains in the fact table.

Ok fixed this issue and reduced the size of my fact table now it only contains the Key identifiers for the 3 dimension tables and 2 facts.

Employee number dimension link
Material Number dimension link
Date dimension link

Traveler is a fact although will never be aggregated and its not something youd add as a dimension table its like a sales order number.

And time is agregated fact and is the time each person spends on that transaction.

After making these adjustments i still get the same issue what is it im missing here?

is there any way you could adjust my PBIX to work so i can see what you have done?

New Relationships

Capture
Capture582Ă—578 16.6 KB

Capture
Capture1239Ă—658 158 KB

EDNA SWitch or IF.pbix (953.5 KB)

Thanks Harsh sorry about this

Dan

13

Hello @Krays23,

Since it’s a model issue please create a new thread for more visibility.

And as of now, I’m really busy due to deadlines but I’m sure members of the forum will look into your model and fix the issue.

Thanks and Warm Regards,
Harsh

1 Like