Countifs for table using related table

[chrismark176]

Hi all,

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

DATAREFERENCE

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.

[jthomson]

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?

[chrismark176]

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

[jthomson]

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]

Hi @chrismark176,

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

Unpivote the table structure of 'DATA' table.

Duplicate [DI] column then split it.

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.

Best regards,
Yuliana Gu

[chrismark176]

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:

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?

[v-yulgu-msft]

Hi @chrismark176,

Please first unpivot DATA table in Query Editor.

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+" )
                )
            )
        )
    )
)

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

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

Best regards,

Yuliana Gu