Forum Discussion

Linda320's avatar
Linda320
Regular Visitor
6 years ago
Solved

Please help on data model

I am newbie in Power BI and need to build a Power BI report from a table.  But I don't have any idea how to build the data model. Please help me! for example below table is a source table which has ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    For your requirement, i add some data to original sample table:

    Add an index column and create a rank column as the same with my last post.

    Create a What If parameter:

    Then try these two measures:

    End of Select Year = 
var EndOf2018 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer]),12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2018,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2018&&MAX('Table'[RecordEnd])>=EndOf2018,MAX('Table'[City]),0))

    End of Following Year = 
var EndOf2019 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer])+1,12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2019,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2019&&MAX('Table'[RecordEnd])>=EndOf2019,MAX('Table'[City]),0))

    Then try this measure to calculate the amount of member before the end of select year:

    Select Year Amount of Member = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)))

    Try these four measures to calculate the amount in each city before the end of following year:

    Following Year in A = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))))

Following Year in B = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))))

Following Year in C = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))))

Following Year in D = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))))

    Choose [City] and above five measures as a table visual, when select one year in slicer, the result shows:

    Here is my changed pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

According to your description, i create sample to test:

For your requirement 1:

Please try this calculated column:

 

Amount of Member = 
var EndOf2018 = DATE(2018,12,31)
return
IF(EndOf2018>='Table'[RecordStart],1,0)

 

Choose [City] and this column as a table visual, the result shows:

For your reqiurement 2:

Please try to add an index column to original table in Query Editor, then create a calculated column:

 

Column = CALCULATE(DISTINCTCOUNT('Table'[Index]),'Table','Table'[Member ID]=EARLIER('Table'[Member ID]),'Table'[Index]<=EARLIER('Table'[Index]))

 

Then try this measure:

 

City Movement = CONCATENATEX(FILTER('Table','Table'[RecordStart]<=DATE(2019,12,31)),[City]," -> ",[Index],ASC)

 

Choose [Member ID] and this measure as a table visual, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

 

 

 

 

Linda320's avatar
Linda320
Regular Visitor
6 years ago

Thanks for your help, Giotto!

The requirement is that user can choose a year, then the power bi report will show in that year, how many members live in each city A,B,C,D. In the meanwhile, the report will automatically display in the following year, among the members in a specific city, how many still lived in the same city and how many moved to other different cities.

for example,

2018   A: 200 members;          2019:  150 of 200 in A;           30 of 200 in B;               20 of 200 in C

 

Thanks again!

 

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    For your requirement, i add some data to original sample table:

    Add an index column and create a rank column as the same with my last post.

    Create a What If parameter:

    Then try these two measures:

    End of Select Year = 
var EndOf2018 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer]),12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2018,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2018&&MAX('Table'[RecordEnd])>=EndOf2018,MAX('Table'[City]),0))

    End of Following Year = 
var EndOf2019 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer])+1,12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2019,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2019&&MAX('Table'[RecordEnd])>=EndOf2019,MAX('Table'[City]),0))

    Then try this measure to calculate the amount of member before the end of select year:

    Select Year Amount of Member = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)))

    Try these four measures to calculate the amount in each city before the end of following year:

    Following Year in A = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))))

Following Year in B = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))))

Following Year in C = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))))

Following Year in D = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))))

    Choose [City] and above five measures as a table visual, when select one year in slicer, the result shows:

    Here is my changed pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi