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
Icon for Community Support rankCommunity 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