cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
chrismark176 Frequent Visitor
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

 

DATA.pngDATAREFERENCE.pngREFERENCE

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
Community Support Team
Community Support Team

Re: Countifs for table using related table

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.
chrismark176 Frequent Visitor
Frequent Visitor

Re: Countifs for table using related table

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?

Super User
Super User

Re: Countifs for table using related table

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 Frequent Visitor
Frequent Visitor

Re: Countifs for table using related table

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

 

 

Super User
Super User

Re: Countifs for table using related table

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

Community Support Team
Community Support Team

Re: Countifs for table using related table

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.