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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
fedorez
Regular Visitor

Dynamic histogram?

Hello!

Could you help me, please.

I have a table with field adr_id and two dates -  date_start and date_end.

I want to show slicer to set the range of dates , and show a histogram with two bins - count records in range of dates, and count records NOT in range of dates...

 

it very simply with static data, we should just create calculated fields....

But I absolutely lost how to do it in dynamic... 

Okay, I may create a table filled with dates (CALENDAR), make a slicer with this table, but - what next?

Calculated fields are not calculated dynamically as I see...

Measure? I think solution is very simple but can't find it )

Any ideas, friends?

2 ACCEPTED SOLUTIONS
v-yuezhe-msft
Employee
Employee

@fedorez,

Create the following measures in your table. Please note that there is no relationship between your table and calendar table.

mindate = MIN('Date'[DateKey])

Maxdate = MAX('Date'[DateKey])

countinrange = CALCULATE(COUNTA(Table[adr_id]),FILTER(Table,Table[date_start]>=[mindate]&&Table[date_end]<=[Maxdate]))

countnotinrange = CALCULATE(COUNTA(Table[adr_id]),FILTER(Table,Table[date_start]<=[mindate]||Table[date_end]>=[Maxdate]))

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@fedorez

You can create one chart to display the measures, see the example below.
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yuezhe-msft
Employee
Employee

@fedorez,

Create the following measures in your table. Please note that there is no relationship between your table and calendar table.

mindate = MIN('Date'[DateKey])

Maxdate = MAX('Date'[DateKey])

countinrange = CALCULATE(COUNTA(Table[adr_id]),FILTER(Table,Table[date_start]>=[mindate]&&Table[date_end]<=[Maxdate]))

countnotinrange = CALCULATE(COUNTA(Table[adr_id]),FILTER(Table,Table[date_start]<=[mindate]||Table[date_end]>=[Maxdate]))

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your answer, @v-yuezhe-msft

 

In your example, countinrange and countnotinrange  - two charts... Is where the way to see this bins in one chart? The reason is scale. When we are using Auto scale mode for Y-axis, we have different scales for out two charts. Course, we can set min and max for Y-axis manually in design mode, but we don't know the limits for Y in real life.... it's a problem 

 

Regards,

Denis

@fedorez

You can create one chart to display the measures, see the example below.
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks a lot!

And one last impotent question.

May I do the same in case of Calculated table , when my Table based on some more tables?

Like this:

Table = GROUPBY(Table2;[name];"days_average";AVERAGEX(CURRENTGROUP();[period]))

@fedorez,

Yes, you can.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you, Lydia.

@fedorez,

If you solve your issue, please accept my replies as answer, that way, other community members could easily find the answer when they get same issues.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors