Return table instead of distinctcount

[Ja-Ju]

Hello!

My measures work quite well, but I am doing calculations and distinctcounts.

Because of this, it is impossible for me, to tell some other people, which data is behind. I can just answer "we have 107 users for this or this purpose", but cannot give them a list.

I would like to change that but am struggling with the DAX functions.

Here is what I am currently doing.

The first measure gives me the count of users, for whom it would be possible to use our software for time booking.

suppliers_able_to_book = 
VAR __COUNT = CALCULATE (
DISTINCTCOUNT ( table[Login_mail] ),
    FILTER(
        KEEPFILTERS(VALUES('table'[End_Datum])),
        AND(
        'table'[End_Date] >= TODAY(),
        'table'[End_Date] < DATE(2099, 11, 23)
        )
    ),
    FILTER(
        KEEPFILTERS(VALUES('table'[Start_Date])),
        AND(
        'table'[Start_Date] <= TODAY(),
        'table'[Start_Date] > DATE(1993, 11, 23)
        )
    ),
    FILTER(
        KEEPFILTERS(VALUES('table'[Login_mail])),
        NOT('table'[Login_mail] IN {BLANK()})
    ),
    FILTER(
        KEEPFILTERS(VALUES('table'[fixed_price])),
        NOT('table'[fixed_price] IN {1})
    )
)

RETURN
  IF(
   NOT ISBLANK(__COUNT),
   __COUNT,
        IF(
        ISBLANK(__COUNT),
        0
        )
  )

The second measure gives me the amount of users, who are actually doing it:

suppliers_booking_hours = 
VAR __COUNT = CALCULATE (
DISTINCTCOUNT ( table[Login_mail] ),
    FILTER(
        KEEPFILTERS(VALUES('table'[End_Date])),
        AND(
        'table'[End_Date] >= TODAY(),
        'table'[End_Date] < DATE(2099, 11, 23)
        )
    ),
    FILTER(
        KEEPFILTERS(VALUES('table'[Start_Date])),
        AND(
        'table'[Start_Date] <= TODAY(),
        'table'[Start_Date] > DATE(1993, 11, 23)
        )
    ),
    FILTER(
        KEEPFILTERS(VALUES('table'[booking_hours])),
        NOT('table'[booking_hours] IN {BLANK()})
    )
)

RETURN
  IF(
   NOT ISBLANK(__COUNT),
   __COUNT,
        IF(
        ISBLANK(__COUNT),
        0
        )
  )

The third measure gives me the difference between these two values. The outcoming is the amount of suppliers, not using our tool for time booking.

supplier_not_booking = 
VAR __COUNT = CALCULATE (
table[supplier_able_to_book] - table[suppliers_booking_hours]
)

RETURN
  IF(
   NOT ISBLANK(__COUNT),
   __COUNT,
        IF(
        ISBLANK(__COUNT),
        0
        )
  )

Now I would like to get a table back with the actual values, who are the people.

I tried to transform these queries, to get a table back instead of a fixed number, but failed on that.

The problem, why it does not work in the PowerBI Desktop UI (or I did not find a way) is, that normal filtering of values does not work here.

I have a table with multiple rows containing:

project, order, supplier, time_booking (yes/no)

The supplier can be there multiple times but I want to get only these ones, that never booked on any order.

If I use the filtering in the UI, I get more than with my measures and I could prove, that there are some included, that booked already on some other element of the list.

Is there someone around who can help me with this?

Thank you very much!

[Anonymous]

Hi  @Ja-Ju ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ja-Ju]

Hello Liu Yang,

thank you for your reply.

- Edit: sample files removed from server - 

The outcome what I would like to acheive is a table with the data behind the value "suppliers_not_booking".

Thank you in advance.

Best regards,

Jakob

[Ja-Ju]

Hello @Anonymous 

Did you have time to look over my questions?

Thank you!

[Ja-Ju · Accepted solution]

For future users with the same problem looking for a solution:

I solved it by creating a table and using the "sum" feature on the data field in order to get the amount of bookings for each user.

In the filter it is possible to add "blank", so only empty rows are displayed. It is necessary to enable "show elements without data", which can be found as option in one of the data fields / columns.