Number of records with pair of values on 1 column (2 rows) and a common value on another column.

[Anonymous]

We have the following data structure:

One fact table with OrderLines where 1product per order = 1 row. And 1 order number >= 1 row (for each product in the order another row is created). We do not have a parent (dimmension) table for Orders, just the numbers in the column OrderNo.

One dimmension table with Products.

The user wants to be able to select and visualize the orders that contain 2 products based on his selection (slicer).

Since we have a lot of rows in the db and a lot of possible orderlines with different products/order we would like to use the basic structure and do this calculation in DAX, based on 2 slicer selections from the dim Products table. We can afford to add another Product table (as dimmension table), but nothing else in the fact table.

You can see the demo report here: https://drive.google.com/file/d/1fdRrs587_mKA31GwiWx0pikqzsbnsi5l/view?usp=sharing

- As you can see order 1001 has both product 1 and 2. the user should select 1 in a slicer, then select 2 in another slicer (or in the same slicer if that is possible) and then visualize the orders that contain both products.

Thank you!

[amitchandak · Accepted solution]

@Anonymous , use this measure with the order number in the visual

measure =
var _cnt = countx(allselected(Products),products[productID])
return
countx(filter(summarize(Orderlines, Orderlines[OrderNo], "_1", distinctcount(Orderlines[productID])),[_1]=_cnt),[OrderNo]

[Anonymous]

@amitchandak  thank you very much for your answer.

Indeed it works great, but I just realized that I need to be able to select based on category too. I have added a ProductCategory column in the Products table. I have tried to adapt the DAX but it didn't work.

This means that if the category 1 and category 2 are selected and there are:

- 1 order with prod1 from cat 1 / prod 3 from cat 2

- 1 order with prod 2 from cat 1 / prod 4 from cat 2

The result should be 2 orders. But since PBI is expecting to see orders with all 4 products (prod 1,2,3,4) at the same time it gives me blank.

I have modified the data source and the PBI report.

Now the measure should have the value 3 in the example below. You can find the report here:

https://drive.google.com/file/d/1y2Qw-9ZDsINk4suMh9JysKoLTgFAjifD/view?usp=sharing

Thank you in advance!

[Anonymous]

@amitchandakbased on your solution I managed to design this query and everything works as expected now. Thank you very much!

Orders with all selected categories =
VAR _cnt =
    COUNTROWS (
        DISTINCT (
            SELECTCOLUMNS (
                OrderLines,
                "UniqueCategories", RELATED ( Products[Product Category] )
            )
        )
    )
RETURN
    COUNTX (
        FILTER (
            SUMMARIZE (
                Orderlines,
                Orderlines[OrderNo],
                "_1", DISTINCTCOUNT ( Products[Product Category] )
            ),
            [_1] = _cnt && _cnt > 1

        ),
        [OrderNo]
    )