Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amsrivastavaa
Helper III
Helper III

Data Range in Power BI

Hi Guys!!

 

I landed into very weird kind of requirement, the same has been detailed below.

 

Source Data : 

 

ProjectTypeYearChannelAmount
P-1Credit2017120
P-2Credit2017130
P-3Credit20170200
P-4Credit20170300
P-1Credit2018120
P-2Credit2018130
P-3Debit2018110
P-1Debit2017120
P-2Debit2017130
P-3Debit2017040
P-4Debit2017050
P-1Debit2018160
P-2Debit2018170
P-3Debit2018180

 

Filters :

1. Channel 

2. Type

3. Year

 

So far, I have created my Power BI report with above mentioned filters (Channel,Type and Year) with below Matrix as output which showing summation of Amount based on Type and Year. 

 

Type20172018
Credit55060
Debit140210

 

Now, My further requirement is :

1- To implement Range filter that holds values like (<25%; 25-50%; 50-75%; 75-100%).

 

Lets say, User has selected Type=Credit and Range<25%, only records where Percenatge is <25% for Type=Credit will be considered as output

 

ProjectTypeYearChannelAmountPercentage
P-1Credit20171203.64
P-2Credit20171305.45
P-3Credit2017020036.36
P-4Credit2017030054.55
P-1Credit201812033.33
P-2Credit201813050.00
P-3Credit201811016.67
P-1Debit201712014.29
P-2Debit201713021.43
P-3Debit201704028.57
P-4Debit201705035.71
P-1Debit201816028.57
P-2Debit201817033.33
P-3Debit201818038.10

 

Data will be available after Type=Credit and Range<25%, will be as shown below

 

ProjectTypeYearChannelAmountPercentage
P-1Credit20171203.64
P-2Credit20171305.45
P-3Credit201811016.67

 

Now, next step would be generate the data for all the Type and available year for these three Projects (for which above data is available after filteration) and then create Matrix based on that data.

 

I.e. need to have data for all the projects (P-1, P-2 and P-3) for Year (2017 and 2018) for all the Type, as shown below

 

ProjectTypeYearChannelAmount
P-1Credit2017120
P-2Credit2017130
P-3Credit20170200
     
P-1Credit2018120
P-2Credit2018130
P-3Credit2018110
P-1Debit2017120
P-2Debit2017130
P-3Debit2017040
     
P-1Debit2018160
P-2Debit2018170
P-3Debit2018180

 

Then Matrix will be created as shown below 

 

Type20172018
Credit25060
Debit90210

 

Note : If there is another filter say Channel=1, then Range will be applied on the data left after filteration of Channel=1 and then rest of the procedure will remain same.

 

Please suggest!!

 

Thanks

Amit 

5 REPLIES 5
amitchandak
Super User
Super User

@amsrivastavaa , I think Dynamic Segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Hi @amitchandak ,

 

I gone through the video and its seems good however I have a bit different requirement than that

 

1. In my case, there is filter channel=1 which filters data and then I need to apply another logic.

2. After filteration, what ever Project left, I need to have data for all project with TYpe and Year and then matrix will form on top of that data.

 

Thanks

Amit

Hi @amitchandak ,

 

I am able to achieve first part of query.

 

Could you please help me find solutions for second part as well.

 

I.e. once I have data with Table visual where Filter based on Measure is used, then how would I load data into another visual with only Project and Year where I have Measure Filter value =1.

 

Please suggest 

 

Thanks

Amit

Hi @amsrivastavaa ,

 

You can add data fields in filters pane. Can you tell me what's your problem in details?

vyadongfmsft_0-1667281369480.png

 

Best regards,

Yadong Fang

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

Hi @v-yadongf-msft ,

 

I have detailed my issue on below url, PFB the same.

https://community.powerbi.com/t5/Desktop/Power-BI/m-p/2876038#M990104

 

Please suggest

 

Thanks 

Amit

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.