Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
chrismark176
Frequent Visitor

Countifs for table using related table

Hi all,

I have two tables and need to create a relationship between both based on multiple columns

 

DATADATAREFERENCEREFERENCE

Each column in the DATA table is a specific drive time to a location. I would like to create a DAX formula that groups the individual times into the labels in the REFERENCE table. All the research I have found on this states a specific column is needed in the DAX to reference. My Excel training is telling me there is a way to create a nested IF statement, but I'm getting stuck.

6 REPLIES 6
jthomson
Solution Sage
Solution Sage

Maybe an approach would be to create an index column in your 0-15, 15-30 etc table starting at zero, and then for each relevant item in your data table, you could create a measure using the quotient function dividing by 15, which'll then correspond with the index column?

Thanks @jthomson. Can you give me an example of how that would work? I'm still very new to BI and DAX.

 

 

I think that would also fall down with your desire not to have many different measures etc for each time column you have unless I'm missing a quick way to duplicate the function, I'm mostly thinking of a quick way to find something that'll correspond with your time groups table

v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @chrismark176,

 

In Query Editor mode, duplicate the [Drive Time Label] column, then, split it by delimiter '-'.

1.PNG

 

Unpivote the table structure of 'DATA' table.

2.PNG

 

Duplicate [DI] column then split it.

4.PNG5.PNG

 

Save and apply all changes, return back to Data View mode. Create calculated tables like below:

Cross Join =
FILTER (
    CROSSJOIN ( 'DATA', 'REFERENCE TABLE' ),
    OR (
        'DATA'[Label] >= 'REFERENCE TABLE'[Low]
            && 'DATA'[Label] < 'REFERENCE TABLE'[High],
        'DATA'[Label] >= 'REFERENCE TABLE'[Low]
            && 'REFERENCE TABLE'[High] = BLANK ()
    )
)

Summarize Table =
SUMMARIZE (
    'Cross Join',
    'Cross Join'[Drive Time Label],
    "Sum value", SUM ( 'Cross Join'[Value] )
)

Result.

6.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-yulgu-msft for this. It solves another problem I had but I may have misrepresented what I needed in this ask:

 

My ultimate objective is to have a column that takes those drive times, and when compared by other segments in the file, then label thier segment. Here is an example of what I am trying to finalize:

 

Drive Time.png

 

My issue is that there are about 100 columns that have drive times and I do not want to create a calculated column for each individual task, but instead want to be able to interchange these columns in the visual and have the DRIVE TIME SEG column recalculate.

 

Can that be done?

Hi @chrismark176,

 

 

Please first unpivot DATA table in Query Editor.

1.PNG2.PNG

 

Save and apply all changes, then, create a calculated column in table view.

 

Drive Time SEG =
IF (
    'DATA'[Value] > 0
        && 'DATA'[Value] <= 15,
    "0-15",
    IF (
        'DATA'[Value] > 15
            && 'DATA'[Value] <= 30,
        "15-30",
        IF (
            'DATA'[Value] > 30
                && 'DATA'[Value] <= 45,
            "30-45",
            IF (
                'DATA'[Value] > 45
                    && 'DATA'[Value] <= 60,
                "45-60",
                IF (
                    'DATA'[Value] > 60
                        && 'DATA'[Value] <= 75,
                    "60-75",
                    IF ( 'DATA'[Value] > 75 && 'DATA'[Value] <= 90, "75-90", "90+" )
                )
            )
        )
    )
)

 

3.PNG

 

Then, add [Type] column into a slicer, use a matrix visual to host data like below.

5.PNG

 

When you change the slicer item, matix will show the DRIVE TIME SEG column conditionally.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.