DAX Query Help

[GuestUser]

HI,

Need Report in below format

Dept                               Bill Count
Medicine                           1
Provision                           3
Medicine + Provision        2

We do not have Medicine + Provision Value in straight forward column

We have data like below ( Bill Number from fact and Depart from Dimension)

Anyone can pls suggest formula to count medicine+provision from below

Bill Number                   Depart

111                           Medcine
333                           Provision
444                         Medicine
                               Provision
555                            Provision
666                          XYZ
                                 ABC
777                         Medicine
                               Provsision
888                         Provision
999                             AAA

[Icey]

Hi @GuestUser ,

Is this problem solved?

Best Regards,

Icey

[Greg_Deckler]

Create a disconnected table with a single column. Let's call it categories:

Categories

Category

Medicine

Provision

Medicine + Provision

You can then create a measure that switches what it does based upon the Category. You need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

That being said, is that really how your data looks? If so, you probably want to use Fill Down in Query Editor for Bill Number.

You should be able to SUMMARIZE by Bill Number. You could then perhaps CONCATENATEX values with " + " and use that as a FILTER for COUNTROWS.

[GuestUser]

Thanks @Greg_Deckler  for replying !!

But , I am not very good in DAX , so if possible could you please help me with the DAX formula 

[Icey]

Hi @GuestUser ,

Based on your description, I create a simple example. Please check if this is what you want:

1. Fact table and Dimension table.

Fact:

Dimension:

Relationship:

2. Enter data. Create a table 'Categories'.

3. Create a measure.

Measure =
SWITCH (
    TRUE (),
    MAX ( Categories[Category] ) = "Medicine", CALCULATE (
        COUNTROWS ( 'Fact' ),
        FILTER ( 'Dimension', 'Dimension'[Depart] = "Medicine" )
    ),
    MAX ( Categories[Category] ) = "Provision", CALCULATE (
        COUNTROWS ( 'Fact' ),
        FILTER ( 'Dimension', 'Dimension'[Depart] = "Provision" )
    ),
    MAX ( Categories[Category] ) = "Medicine + Provision", CALCULATE (
        COUNTROWS ( 'Fact' ),
        FILTER (
            'Dimension',
            'Dimension'[Depart] = "Medicine"
                || 'Dimension'[Depart] = "Provision"
        )
    )
)

4. Create a table visual.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[GuestUser]

Thanks @Icey !!

Thanks for your help !!

But it should not count the Transaction which has only Medicine in "Medicine+Provision" , same for provision as well

So output  should be

Medicine - 1

Provision - 3

Medinie +Provision - 2

[Icey]

Hi @GuestUser ,

Maybe you can try to create a measure like this:

CountofBill = 
VAR t =
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN (
                'Fact',
                SELECTCOLUMNS (
                    'Dimension',
                    "Depart2", 'Dimension'[Depart],
                    "ID2", 'Dimension'[ID]
                )
            ),
            'Fact'[ID] = [ID2]
        ),
        "Depart3", SWITCH ( TRUE (), [Depart2] = "Medicine", 1, [Depart2] = "Provision", 2 )
    )
VAR t2 =
    ADDCOLUMNS (
        VALUES ( 'Fact'[Bill Number] ),
        "SUM_", SUMX ( FILTER ( t, [Bill Number] = EARLIER ( [Bill Number] ) ), [Depart3] )
    )
RETURN
    SWITCH (
        TRUE (),
        MAX ( Categories[Category] ) = "Medicine", COUNTROWS ( FILTER ( t2, [SUM_] = 1 ) ),
        MAX ( Categories[Category] ) = "Provision", COUNTROWS ( FILTER ( t2, [SUM_] = 2 ) ),
        MAX ( Categories[Category] ) = "Medicine + Provision", COUNTROWS ( FILTER ( t2, [SUM_] = 3 ) )
    )

Best Regards,

Icey

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

[GuestUser]

Thanks @Icey 

But when i actualy used the measure on my actual data

I am getting same count 1 for all.

Not sure whats going wrong..anyways will check further and update

[GuestUser]

Hi,

The above measure is taking a  bit long time for generating only one months data..is there any way to tune it ? We need to generate data for multiple months...

[Icey]

Hi @GuestUser ,

I consulted others. How about this one?

Bill Count = 
VAR t =
    SUMMARIZE (
        'Fact',
        'Fact'[Bill Number],
        "DistinctID", DISTINCTCOUNT ( 'Fact'[ID] ),
        "IDs", CONCATENATEX ( 'Fact', 'Fact'[ID] )
    )
RETURN
    SUMX (
        DISTINCT ( 'Categories'[Category] ),
        VAR depts =
            CALCULATETABLE (
                DISTINCT ( 'Dimension'[Depart] ),
                FILTER ( 'Dimension', CONTAINSSTRING ( [Category], 'Dimension'[Depart] ) )
            )
        VAR idts =
            CALCULATETABLE (
                DISTINCT ( 'Dimension'[ID] ),
                FILTER ( 'Dimension', CONTAINSSTRING ( [Category], 'Dimension'[Depart] ) )
            )
        RETURN
            COUNTROWS (
                FILTER (
                    t,
                    VAR s = [IDs] RETURN [DistinctID] = COUNTROWS ( depts )
                        && SUMX ( idts, IF ( CONTAINSSTRING ( s, [ID] ), 1, BLANK () ) )
                            = COUNTROWS ( idts )
                )
            )
    )

Best Regards,

Icey

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