Please help how to show data in range between with dax

[Yutthakorn]

Table	Invoicedt
Ocmnum	Cash	Name
1	100	AA
2	0	BB
3	500	AA
4	4000	CC
5	3500	DD
6	10000	AA

That example data  and i generate series 

i want to show data in grid count of range in data

Name	0-1000	1001-2000	2001-3000	4000-4001
AA	2	0	0	0
BB	1		0	0
CC	0	0	0	1
DD	0	0	0	1

Cash Over = 

Var CashOver = SELECTEDVALUE('Invoicedt'[Cash])

Var CashRange = SELECTEDVALUE(Series[Value])

Var  NameBilling = SELECTEDVALUE(Invoicedt[Name])

Return

        CALCULATE(

            DISTINCTCOUNT(Invoicedt[Ocmnum]),

            FILTER( Invoicedt, CashOver > CashRange && CashOver <= CashRange+1000 && Invoicedt[Name] = NameBilling

            )

    )

i write measure but not show data in grid 

[HiraNegi · Accepted solution]

Hi @Yutthakorn ,

Ideally you should generate series in Power Query and then merge join with your data table based on the range. But if you like a simple workaround then you can follow below steps.

1. Create series table in DAX with increment of 1.

2. Create a group/bin on the Series values with increment of 1000.

This is what you will get in the Series Table.

3. Create relation between Series and Data table. If you have decimal values then you can round it to nearest whole number to ensure all data gets to join with Series table.

4. Now you can create your report using the bins and count from data table.

Hope this helps.

Regards,

Hira Negi

[vicky_]

Rather than using a measure, you can acheive the same thing by using grouping / binning. Tutorial here: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning