How to return customers with no transactions

[a4apple]

Hello Everyone. Please see my problem below and help me if you can.

I have created two tables in Power BI. One is transactions with Customer ID, Sales Amount, Date. The other is the list of Customers. CustomerID is the Join Key between these two tables.

I have create two measures i) Count of Transactions = COUNT(CustomerID) and ii) SalesAmount = SUM(Sales Amount)

Now I try to create a simple report using Table Visual. It shows me only the matching customer ID's and their counts.

I want to see users with 0 Transactions (basically like a left join) as well. I achieved it by using a IF(ISBLANK([count of transactions]), 0, [count of transactions]). This works perfectly.

Now I try to create a Dynamic Segmentation by counts of transactions. 0, 1-5, 6-10, >10 are the ranges. I have followed the steps on SQL BI  and it works fine if I don't use the ISBLANK one. If I use that measure, I am getting all records as 0 if they are not in the segment I am looking for. 

"https://www.powerpivotpro.com/2014/11/new-twist-for-dynamic-segmentation-variable-grain-range-select..." this is link I followed to do this.

If there is any other way to show customers count as 0 who didn't make any purchases or not present in the order table, please let me know.

[v-haibl-msft]

@a4apple

I’m not sure what is the measure formula of Dynamic Segmentation you used. But according to your description, I created several tables and measures. I can get the final Dynamic Segmentation as below. Maybe you can try the following measure formula I provided.

Count of Transactions = 
COUNT ( transactions[CustomerID] )

Transactions = 
IF ( ISBLANK ( [count of transactions] ), 0, [count of transactions] )

RangeMeasure = 
    CALCULATE (
        VALUES ( RangeTable[Range] ),
        FILTER (
            RangeTable,
            transactions[Transactions] >= RangeTable[RangeMin]
                && transactions[Transactions] <= RangeTable[RangeMax]
        )
)

Best Regards,

Herbert

[a4apple]

Thank you for your detailed reply @v-haibl-msft . The issue is I want to create a bar-chart that will give me the total number of customers per range and this is giving the same number of Customers for every Range because of the ISBLANK operator. Any thoughts?

[a4apple]

I get this when I work with the data you have created @v-haibl-msft. But I am trying to get the actual counts instead. I think this is happening because of the ISBLANK check.

[Vvelarde · Accepted solution]

@a4apple

Try with this:

Count =
    IF (
        ISBLANK ( CALCULATE ( COUNTROWS ( Transactions ); RELATEDTABLE ( Customers ) ) ),
        0,
        CALCULATE ( COUNTROWS ( Transactions ), RELATEDTABLE ( Customers ) )
    )

Result =
    CALCULATE (
        COUNTROWS ( Customers ),
        FILTER (
            SUMMARIZE ( Customers, Customers[CustomerID], "CountofCustomers"; [Count] );
            [CountofCustomers] >= VALUES ( RangeTable[RangeMin] )
                && [CountofCustomers] <= VALUES ( RangeTable[RangeMax] )
        )
    )

[a4apple]

@Vvelarde This works perfect but please see my other question. Thank you for your help.

[Vvelarde]

@a4apple

I use the Herbert Sample data with 7 customers, and show the results Ok. Maybe you have a different structure that affect the result. Put sample data and i try to see what is happen.

[a4apple]

@Vvelarde

I tried with the DAX you have provided, but I get this for the Range by result. I am not able to get it to work for some reason. Can you please share the PBIX if you can. I have used the same data provided in the above posts. This is what I have used.

Result =
CALCULATE (
COUNTROWS ( Customer ),
FILTER (
SUMMARIZE ( Customer, Customer[CustomerID], "CountofCustomers", [Number Of Transactions] ),
[CountofCustomers] >= VALUES ( Range[RangeMin] )
&& [CountofCustomers] <= VALUES ( Range[RangeMax] )
)
)

Thanks in advance.

[a4apple]

@Vvelarde Thank you so much. This works perfectly for the visual I am doing now. 

Just a quick question, I am not able to see the data in a table type visual when I use this measure. It only works with the graph. Any reason behind that? What if in future, I want be able to drill down to see the actual stores in that Range. Is that possible? Any ideas on that will be helpful for me.

Thank you again.

[Anonymous]

What you're doing seems to makes sense.  To fully understand the issue, can you post the measure you are using to count by range,  a screenshot of the problem output and some sample data that is not working for that output?